Re: initialize and use variable in query

On Saturday, December 29, 2018, Glenn Schultz <glenn(at)bondlab(dot)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.