Thread: initialize and use variable in query
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 $$;
select
fnmloan.loanseqnum
,fnmloan.currrpb
from
fnmloan
join
fnmloan_data
on
fnmloan_data.loanseqnum = fnmloan.loanseqnum
where
fnmloan.fctrdt = (select * from startdate)
limit 10
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;BEGINstartdate := (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:
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.
Just refactor DO block to function that returns row set and put SELECT inside. > 29 дек. 2018 г., в 18:40, Glenn Schultz <glenn@bondlab.io> написал(а): > > 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 justneed to work this out. I followed the docs and thought I needed something like this. But does not work-maybe I havemisunderstood. 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 $$; > > select > fnmloan.loanseqnum > ,fnmloan.currrpb > from > fnmloan > > join > fnmloan_data > on > fnmloan_data.loanseqnum = fnmloan.loanseqnum > > where > fnmloan.fctrdt = (select * from startdate) > > limit 10