PL/pgSQL doesn't support variables in queries?
Heya folks :) ms-sql person here migrating over to pgsql. One of the first thing's I noticed with pgsql (or more specifically, PL/pgSQL) is that it doesn't support "variables" in a query? for example, here's some T-SQL: DECLARE @fkId INTEGER SELECT @fkId = fkId FROM SomeTable WHERE id = 1 -- and then do something with that value.. SELECT * FROM AnotherTable WHERE Id = @fkId SELECT * FROM YetAnotherTable WHERE FKId = @fkId -- etc.. If I have this information correct, has this concept ever been discussed before or considered to be included in PL/pgSQL ? Thank you kindly for any help/conversations on this topic. Sincere apologies if this is not the correct forum/list to ask this question. Regards, JA.
Re: PL/pgSQL doesn't support variables in queries?
Oh wow folks! I totally misunderstood the docs then. (I also tried to Read The Manual before I posted here, too :blush:) I must admit, I did try doing something like you suggested Erik. I tried things like: DO $$ DECLARE v_application_id uuid; BEGIN SELECT application_id INTO v_application_id FROM applications WHERE code = 'pg-test-cc'; SELECT * FROM application_foo WHERE application_id = v_application_id; -- more SELECT * FROM child tables END $$; but that never worked, with warning: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function inline_code_block line 7 at SQL statement SQL state: 42601 Which is why i (incorrectly?) thought this cannot be done? So is there another trick to doing this instead? Is it maybe via the v_record "record" variable instead? -JA- On Wed, 3 May 2023 at 22:39, Erik Wienhold wrote: > > On 03/05/2023 14:25 CEST J.A. > wrote: > > > > ms-sql person here migrating over to pgsql. One of the first thing's I > noticed > > with pgsql (or more specifically, PL/pgSQL) is that it doesn't support > > "variables" in a query? > > > > for example, here's some T-SQL: > > > > DECLARE @fkId INTEGER > > > > SELECT @fkId = fkId FROM SomeTable WHERE id = 1 > > > > -- and then do something with that value.. > > > > SELECT * FROM AnotherTable WHERE Id = @fkId > > SELECT * FROM YetAnotherTable WHERE FKId = @fkId > > -- etc.. > > plpgsql does support variable declarations [0] but does not use any special > notation like T-SQL. An equivalent to your example would be: > > DO $$ > DECLARE > v_fkid int; > v_rec record; > BEGIN > SELECT fkid INTO v_fkid FROM SomeTable WHERE id = 1; > SELECT * INTO v_rec FROM AnotherTable WHERE Id = v_fkid; > -- Do something with v_rec ... > END $$; > > Prefixing variable names with v_ is just a convention to avoid ambiguous > column > references (assuming that column names are not prefixed with v_) [1]. > > [0] https://www.postgresql.org/docs/current/plpgsql-declarations.html > [1] > https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST > > -- > Erik >
Re: PL/pgSQL doesn't support variables in queries?
Ah - I think I'm starting to follow. what i was _trying_ to do is this get value from a column and stick it into a variable. now select * from a _number_ of tables and return a -multi recordsets- from this single query. I'm not sure if that is the same terminology, in pgsql? So is this possible? -JA- On Wed, 3 May 2023 at 23:29, Tom Lane wrote: > "J.A." writes: > > I must admit, I did try doing something like you suggested Erik. I tried > > things like: > > > DO $$ > > DECLARE > > v_application_id uuid; > > BEGIN > > SELECT application_id INTO v_application_id FROM applications > > WHERE code = 'pg-test-cc'; > > > SELECT * FROM application_foo WHERE application_id = > > v_application_id; > > -- more SELECT * FROM child tables > > > END $$; > > > but that never worked, with warning: > > > ERROR: query has no destination for result data HINT: If you want to > > discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL > > function inline_code_block line 7 at SQL statement SQL state: 42601 > > Note that that is complaining about your second try, not your first. > You need to put the result of the SELECT somewhere. INTO is fine > if it's a single-row result. Otherwise, consider looping through > the result with a FOR loop. Again, there are plenty of examples > in the manual. > > regards, tom lane >
