Обсуждение: PL/pgSQL doesn't support variables in queries?

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

> 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

> 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

"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

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