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 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 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 toSELECT INTO
.CREATE TABLE AS
is the recommended syntax, since this form ofSELECT INTO
is not
available in ECPG or PL/pgSQL, because they interpret theINTO
clause
differently. Furthermore,CREATE TABLE AS
offers a superset of the
functionality provided bySELECT 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
SQL Server Equivalent to MySQL's Explain
Running Total by Grouped Records in Table
Join a Count Query on Generate_Series() and Retrieve Null Values as '0'
H2 Database Column Name "Group" Is a Reserved Word
How to See Cakephp's SQL Dump in the Controller
Select Not in Multiple Columns
How to Remove White Space Characters from a String in SQL Server
SQL Datediff - Find Datediff Between Rows
SQL Server: Replace Invalid Xml Characters from a Varchar(Max) Field
Ora-01861: Literal Does Not Match Format String
Return All Possible Combinations of Values Within a Single Column in SQL
Why Is Rand() Not Producing Random Numbers
How to Get the Actual Stored Procedure Line Number from an Error Message