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?

Titi

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.

Seguidilla

Answer 2

Yes,

SELECT name,family INTO cName, cFamily FROM "CommonUsersModel";

OR

SELECT INTO cName, cFamily name,family  FROM "CommonUsersModel"

Cymogene

Answer 3

    DO 
  $$
  DECLARE var_one UUID; 
    var_two UUID;  
    var_three UUID;  
    var_four UUID;  
    var_five UUID;
 BEGIN
     Select INTO var_one,var_two,var_three,var_four,var_five
              h.foocol1,  
              h.foocol2,  
              h.foocol3,  
              h.foocol4,  
              h.foocol5
    from footable h
    where h.fooid = 2;

END;
$$;

This is how you can set multiple variables using SELECT INTO

Thanks !

Renewal