initialize and use variable in query

On 29/12/2018 15:40, Glenn Schultz wrote:

All,

I need to initialize a variable and then use it in query.
Ultimately this will part of a recursive CTE but for now I just need to
work this out.  I followed the docs and thought I needed something like
this.  But does not work-maybe I have misunderstood.  Is this possible?

Hi there,

What does "does not work" mean? What error do you get?

SET max_parallel_workers_per_gather = 8;
SET random_page_cost = 1;
SET enable_partitionwise_aggregate = on;
Do $$
Declare startdate date;
BEGIN
startdate := (select max(fctrdt) from fnmloan);
END $$;

A couple of things off the top of my head:

(i) I think you need "language plpgsql" (or whatever) after the DO block.

(ii) That assignment in the DO should probably be:

select max(fctrdt) into startdate from fnmloan;

I hope this helps.

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

On Saturday, December 29, 2018, Glenn Schultz <glenn@bondlab.io> wrote:

All,

I need to initialize a variable and then use it in query. Ultimately this
will part of a recursive CTE but for now I just need to work this out. I
followed the docs and thought I needed something like this. But does not
work-maybe I have misunderstood. Is this possible?

SET max_parallel_workers_per_gather = 8;
SET random_page_cost = 1;
SET enable_partitionwise_aggregate = on;
Do $$
Declare startdate date;
BEGIN
startdate := (select max(fctrdt) from fnmloan);
END $$;

The stuff in the DO block is plpgsql, the stuff outside is SQL. SQL cannot
see plpgsql variables. And the plpgsql variables cease to exist at the end
of the block anyway.

You need to use SET or set_config with a custom variable (namespaced) in
the SQL portion of the script (examples are out there somewhere, not able
to research for you presently). Or maybe use psql and its
features/variables...

David J.

On Saturday, December 29, 2018, Ray O'Donnell <ray@rodonnell.ie> wrote:

A couple of things off the top of my head:

Sorry but, no.

(i) I think you need "language plpgsql" (or whatever) after the DO block.

As the docs state plpgsql is the default for a DO block lacking a language
specifier.

(ii) That assignment in the DO should probably be:

select max(fctrdt) into startdate from fnmloan;

The original form is perfectly valid plpgsql;

The DO block worked just fine. It’s just that everything it did was
discarded at the end of it because nothing that permanently affected the
parent SQL session happened.

David J.

Thanks!

On Sat, Dec 29, 2018 at 10:06 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

Just refactor DO block to function that returns row set and put SELECT inside.