pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgSQL doesn't support variables in queries?
9+ messages / 6 participants
[nested] [flat]
* PL/pgSQL doesn't support variables in queries?
@ 2023-05-03 12:25 J.A. <postgresql@world-domination.com.au>
2023-05-03 12:31 ` Re: PL/pgSQL doesn't support variables in queries? hubert depesz lubaczewski <depesz@depesz.com>
2023-05-03 12:39 ` Re: PL/pgSQL doesn't support variables in queries? Erik Wienhold <ewie@ewie.name>
0 siblings, 2 replies; 9+ messages in thread
From: J.A. @ 2023-05-03 12:25 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PL/pgSQL doesn't support variables in queries?
2023-05-03 12:25 PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
@ 2023-05-03 12:31 ` hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 0 replies; 9+ messages in thread
From: hubert depesz lubaczewski @ 2023-05-03 12:31 UTC (permalink / raw)
To: J.A. <postgresql@world-domination.com.au>; +Cc: pgsql-general@lists.postgresql.org
On Wed, May 03, 2023 at 10:25:55PM +1000, J.A. wrote:
> 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
Sure it does.
There is nothing relating to "@" character, though.
You can easily find examples in docs:
https://www.postgresql.org/docs/current/plpgsql-structure.html
depesz
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PL/pgSQL doesn't support variables in queries?
2023-05-03 12:25 PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
@ 2023-05-03 12:39 ` Erik Wienhold <ewie@ewie.name>
2023-05-03 12:51 ` Re: PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
1 sibling, 1 reply; 9+ messages in thread
From: Erik Wienhold @ 2023-05-03 12:39 UTC (permalink / raw)
To: J.A. <postgresql@world-domination.com.au>; pgsql-general@lists.postgresql.org
> On 03/05/2023 14:25 CEST J.A. <postgresql@world-domination.com.au> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PL/pgSQL doesn't support variables in queries?
2023-05-03 12:25 PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
2023-05-03 12:39 ` Re: PL/pgSQL doesn't support variables in queries? Erik Wienhold <ewie@ewie.name>
@ 2023-05-03 12:51 ` J.A. <postgresql@world-domination.com.au>
2023-05-03 13:26 ` Re: PL/pgSQL doesn't support variables in queries? Erik Wienhold <ewie@ewie.name>
2023-05-03 13:28 ` Re: PL/pgSQL doesn't support variables in queries? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: J.A. @ 2023-05-03 12:51 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-general@lists.postgresql.org
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 <ewie@ewie.name> wrote:
> > On 03/05/2023 14:25 CEST J.A. <postgresql@world-domination.com.au>
> 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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PL/pgSQL doesn't support variables in queries?
2023-05-03 12:25 PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
2023-05-03 12:39 ` Re: PL/pgSQL doesn't support variables in queries? Erik Wienhold <ewie@ewie.name>
2023-05-03 12:51 ` Re: PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
@ 2023-05-03 13:26 ` Erik Wienhold <ewie@ewie.name>
1 sibling, 0 replies; 9+ messages in thread
From: Erik Wienhold @ 2023-05-03 13:26 UTC (permalink / raw)
To: J.A. <postgresql@world-domination.com.au>; +Cc: pgsql-general@lists.postgresql.org
> On 03/05/2023 14:51 CEST J.A. <postgresql@world-domination.com.au> wrote:
>
> 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?
plpgsql requires you to either store query results in variables or discard them
as the hint in the error message says. PERFORM is mainly used to execute
functions for their side-effects only, e.g. PERFORM pg_reload_conf(), or execute
a query where you only want to tell if rows were found by checking special
variable FOUND afterwards.
> So is there another trick to doing this instead? Is it maybe via the v_record
> "record" variable instead?
Depends on what you want to do with those application_foo rows. SELECT INTO
only considers the first row. I assume you want to loop over the entire result
set. Then you must use FOR v_rec IN <query> LOOP:
DO $$
DECLARE
v_application_id uuid;
v_rec record;
BEGIN
SELECT application_id INTO v_application_id FROM applications WHERE code = 'pg-test-cc';
FOR v_rec IN
SELECT * FROM application_foo WHERE application_id = v_application_id
LOOP
RAISE NOTICE 'v_rec = %', v_rec; -- Prints each result.
END LOOP;
END $$;
--
Erik
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PL/pgSQL doesn't support variables in queries?
2023-05-03 12:25 PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
2023-05-03 12:39 ` Re: PL/pgSQL doesn't support variables in queries? Erik Wienhold <ewie@ewie.name>
2023-05-03 12:51 ` Re: PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
@ 2023-05-03 13:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-03 14:34 ` Re: PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2023-05-03 13:28 UTC (permalink / raw)
To: J.A. <postgresql@world-domination.com.au>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
"J.A." <postgresql@world-domination.com.au> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PL/pgSQL doesn't support variables in queries?
2023-05-03 12:25 PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
2023-05-03 12:39 ` Re: PL/pgSQL doesn't support variables in queries? Erik Wienhold <ewie@ewie.name>
2023-05-03 12:51 ` Re: PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
2023-05-03 13:28 ` Re: PL/pgSQL doesn't support variables in queries? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-05-03 14:34 ` J.A. <postgresql@world-domination.com.au>
2023-05-03 14:40 ` Re: PL/pgSQL doesn't support variables in queries? David G. Johnston <david.g.johnston@gmail.com>
2023-05-03 14:56 ` Re: PL/pgSQL doesn't support variables in queries? Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 9+ messages in thread
From: J.A. @ 2023-05-03 14:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
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 <tgl@sss.pgh.pa.us> wrote:
> "J.A." <postgresql@world-domination.com.au> 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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PL/pgSQL doesn't support variables in queries?
2023-05-03 12:25 PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
2023-05-03 12:39 ` Re: PL/pgSQL doesn't support variables in queries? Erik Wienhold <ewie@ewie.name>
2023-05-03 12:51 ` Re: PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
2023-05-03 13:28 ` Re: PL/pgSQL doesn't support variables in queries? Tom Lane <tgl@sss.pgh.pa.us>
2023-05-03 14:34 ` Re: PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
@ 2023-05-03 14:40 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2023-05-03 14:40 UTC (permalink / raw)
To: J.A. <postgresql@world-domination.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
The convention on these lists is to inline or, at worse, bottom-post.
On Wed, May 3, 2023 at 7:34 AM J.A. <postgresql@world-domination.com.au>
wrote:
> 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?
>
The output of a set-returning function (srf) is a single tabular result.
If you want to produce multiple tabular results you would need to either
serialize them (say into jsonb) or assign a cursor name to each and then
reference them by name.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PL/pgSQL doesn't support variables in queries?
2023-05-03 12:25 PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
2023-05-03 12:39 ` Re: PL/pgSQL doesn't support variables in queries? Erik Wienhold <ewie@ewie.name>
2023-05-03 12:51 ` Re: PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
2023-05-03 13:28 ` Re: PL/pgSQL doesn't support variables in queries? Tom Lane <tgl@sss.pgh.pa.us>
2023-05-03 14:34 ` Re: PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
@ 2023-05-03 14:56 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 9+ messages in thread
From: Adrian Klaver @ 2023-05-03 14:56 UTC (permalink / raw)
To: J.A. <postgresql@world-domination.com.au>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
On 5/3/23 07:34, J.A. wrote:
> 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?
Many things are possible and they are covered in the documentation:
https://www.postgresql.org/docs/current/plpgsql.html
Read through that and you will find your questions answered.
>
> -JA-
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-05-03 14:56 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-03 12:25 PL/pgSQL doesn't support variables in queries? J.A. <postgresql@world-domination.com.au>
2023-05-03 12:31 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-05-03 12:39 ` Erik Wienhold <ewie@ewie.name>
2023-05-03 12:51 ` J.A. <postgresql@world-domination.com.au>
2023-05-03 13:26 ` Erik Wienhold <ewie@ewie.name>
2023-05-03 13:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-03 14:34 ` J.A. <postgresql@world-domination.com.au>
2023-05-03 14:40 ` David G. Johnston <david.g.johnston@gmail.com>
2023-05-03 14:56 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox