Re: PL/pgSQL doesn't support variables in queries?

> On 03/05/2023 14:51 CEST J.A. <postgresql(at)world-domination(dot)com(dot)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