sql - 具有多个属性的 SELECT INTO

Question

这条指令有效:

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

但我想这样使用:

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

如何只用一条SELECT INTO指令设置多个变量？

Answer 1

在 PL/pgSQL 中，您可以SELECT INTO 一次直接选择任意数量的变量。你只是把语法倒过来了:

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

您有关键字 INTO 后跟一个目标变量列表，并且您有一个相应的 SELECT 列表。 INTO 子句的目标可以是(引用 the manual here ):

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

还有:

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

这不要混淆与SELECT INTO in the SQL dialect of Postgres - 不应再使用。它违背了标准 SQL，最终很可能会被删除。该手册积极劝阻其继续使用:

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