Re: PL/pgSQL doesn't support variables in queries? - Mailing list pgsql-general

From J.A.
Subject Re: PL/pgSQL doesn't support variables in queries?
Date
Msg-id CALT+_gsMv7JuK1WYH9Ubch6d=i6HEOo_02V=ym48=7LiTVa5RA@mail.gmail.com
Whole thread Raw
In response to Re: PL/pgSQL doesn't support variables in queries?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: PL/pgSQL doesn't support variables in queries?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: PL/pgSQL doesn't support variables in queries?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Sergey Cherevko
Date:
Subject: iso-8859-1 postgres ssl error "could not accept SSL connection: Success" and timeout
Next
From: "David G. Johnston"
Date:
Subject: Re: PL/pgSQL doesn't support variables in queries?