sql - SELECT INTO with more than one attribution

Question

This instruction works:

SELECT INTO unsolvedNodes array_agg(DISTINCT idDestination)
FROM road 
WHERE idOrigin = ANY(solvedNodes)
AND NOT (idDestination = ANY(solvedNodes));

But I would like to use something this way:

SELECT INTO unsolvedNodes array_agg(DISTINCT idDestination), lengths array_agg(length)
FROM road
WHERE idOrigin = ANY(solvedNodes)
AND NOT (idDestination = ANY(solvedNodes));

How to use only one SELECT INTO instruction to set multiple variables?

Answer 1

In PL/pgSQL you can SELECT INTO as many variables at once as you like directly. You just had the syntax backwards:

SELECT INTO unsolvedNodes, lengths 
       array_agg(DISTINCT idDestination), array_agg(length)
FROM   road
WHERE  idOrigin = ANY(solvedNodes)
AND    NOT (idDestination = ANY(solvedNodes));

You have the keyword INTO followed by a list of target variables, and you have a corresponding SELECT list. The target of the INTO clause can be (quoting the manual here):

...a record variable, a row variable, or a comma-separated list of simple variables and record/row fields.

Also:

The INTO clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just after the list of select_expressions in a SELECT command, or at the end of the command for other command types. It is recommended that you follow this convention in case the PL/pgSQL parser becomes stricter in future versions.

This is not to be confused with SELECT INTO in the SQL dialect of Postgres - which should not be used any more. It goes against standard SQL and will eventually be removed, most likely. The manual actively discourages its continued use:

It is best to use CREATE TABLE AS for this purpose in new code.