Select into with More Than One Attribution

SELECT INTO with more than one attribution

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.

Syntax error at or near ' , ' while trying to SELECT INTO

The error message indicates that you run the statement as plain SQL. But it only makes sense inside a PL/pgSQL block. You explicitly stated that it's for:

assigning to multiple variables

which only makes sense inside procedural language code as there are no variable assignments in plain SQL. SELECT INTO inside PL/pgSQL has a different meaning than SQL SELECT INTO - the use of which is generally discouraged. The manual:

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS
is the recommended syntax, since this form of SELECT INTO is not
available in ECPG or PL/pgSQL, because they interpret the INTO clause
differently. Furthermore, CREATE TABLE AS offers a superset of the
functionality provided by SELECT INTO.

There's nothing wrong with your placement of the INTO clause - when used in PL/pgSQL like you tagged.

Related:

  • SELECT INTO with more than one attribution

How to make attribution with SQL

In MySQL 8+, you can use window functions:

select t.*,
(case when count(*) over (partition by id) = 1
then 1.0
when count(*) over (partition by id) = 2
then 0.5
when row_number() over (partition by id order by ses.num) = 1
then 0.4
when row_number() over (partition by id order by ses.num desc) = 1
then 0.4
else 0.2 / (count(*) over (partition by id) - 2)
end)
from t;


Related Topics



Leave a reply



Submit