`SELECT INTO` multiple variables in PostgreSQL

Question

I'm having issue assigning multiple variables at once. Running the code below

SELECT v1, v2 INTO x, y FROM (VALUES (1, 2)) AS t (v1, v2);

throws an error:

ERROR: syntax error at or near "," LINE 1: select v1, v2 into x, y from (values (1,2)) as t (v1, v2);

Link to dbfiddle:

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=98285b190de7871354ccb444d17eb25f

Would anyone be able to help?

Thank you.

Answer 1

You can assign multiple values to multiple variables with an SELECT INTO statement at once as shown below:

DO $$
DECLARE 
  x INT;
  y INT;
BEGIN
  SELECT * INTO x, y FROM (VALUES (1, 2));
  RAISE INFO 'x=% y=%', x, y; -- x=1 y=2
END $$;

Or, you can use an AS clause. *Omitting t. and AS still work:

DO $$
DECLARE 
  x INT; 
  y INT;
BEGIN                                           -- ↓↓↓↓↓↓↓↓↓↓↓↓↓
  SELECT t.v1, t.v2 INTO x, y FROM (VALUES (1, 2)) AS t (v1, v2);
  RAISE INFO 'x=% y=%', x, y; -- x=1 y=2
END $$;

Or, you can set an INTO clause to the end:

DO $$
DECLARE 
  x INT;
  y INT;
BEGIN                        -- ↓↓↓↓↓↓↓↓↓
  SELECT * FROM (VALUES (1, 2)) INTO x, y;
  RAISE INFO 'x=% y=%', x, y; -- x=1 y=2
END $$;

Or, you can set an INTO clause to the end. *Omitting t. and AS still work:

DO $$
DECLARE 
  x INT; 
  y INT;
BEGIN                                               -- ↓↓↓↓↓↓↓↓↓
  SELECT t.v1, t.v2 FROM (VALUES (1, 2)) AS t (v1, v2) INTO x, y;
  RAISE INFO 'x=% y=%', x, y; -- x=1 y=2
END $$;

Answer 2

SQL variables in Postgres are not supported. You can use this kind of assignment in PL/pgSQL language, in a function or an anonymous code block, e.g.:

do $$
declare 
    x int; 
    y int;
begin
    select v1, v2 into x, y 
    from (values (1,2)) as t (v1, v2);
end $$;

db<>fiddle.

Answer 3

Use the aliases separately right next to each output as follows:

select v1 as x, v2 as y from (values (1,2)) as t(v1,v2);