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 <ewie(at)ewie(dot)name> wrote:

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