Re: initialize and use variable in query

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Saturday, December 29, 2018, Glenn Schultz <glenn@xxxxxxxxxx> 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.