pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PL/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