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.
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 $$;
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);
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 $$;
