sqldatabasepostgresqlplpgsqlselect-into

`SELECT INTO` multiple variables in PostgreSQL

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.

Solution

  • 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.