选择多个属性 [英] SELECT INTO with more than one attribution

问题描述

此说明有效:

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));

如何仅使用一条"SELECT INTO"指令来设置多个变量?

How to use only one "SELECT INTO" instruction to set multiple variables?

推荐答案

在 PL/pgSQL 中，您可以直接SELECT INTO 一次任意数量的变量.您只是向后使用了语法:

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));

您具有关键字INTO，后跟目标变量列表，并且具有相应的SELECT列表. INTO子句的目标可以是(引用该手册这里):

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.

也:

INTO子句几乎可以出现在SQL命令中的任何位置. 通常，它写在列表的前面或后面 SELECT命令中或命令末尾的select_expressions 对于其他命令类型.建议您遵循此 约定，以防PL/pgSQL解析器在将来的版本中变得更严格.

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.

这与 SELECT INTO Postgres的SQL方言-没有人应该再使用.它违反标准SQL，最终很有可能会被删除.该手册积极劝阻其继续使用:

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:

为此，最好在新代码中使用CREATE TABLE AS.

这篇关于选择多个属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！