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?
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 aSELECT
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 nobody should be using 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.