如何转换实体以在PostgreSQL中设置 [英] How to cast entity to set in PostgreSQL

问题描述

使用Postgres 9.3，我发现我可以执行以下操作：

Using Postgres 9.3, I found out that I can perform something like this:

SELECT generate_series(1,10);

但是我不能这样做：

SELECT (SELECT generate_series(1,10));

我能以某种方式将SELECT结果转换为 setof int 可以将其与 generate_series（）的结果相同？

Can I somehow cast SELECT result to setof int to use it same as result from generate_series()?

为什么我可以使用result到底发生了什么从函数而不是从 SELECT ？

What exactly is happening there why I can use result from function but not from SELECT?

推荐答案

您的第一个表单是Postgres的非标准功能。它允许 SELECT 列表中的SRF（设置返回函数），该列表会扩展为多行：

Your first form is a non-standard feature of Postgres. It allows SRF (Set Returning Functions) in the SELECT list, which are expanded to multiple rows:

• PostgreSQL中是否有类似zip（）的函数，该函数结合了两个数组？

• Is there something like a zip() function in PostgreSQL that combines two arrays?

注意：这是可行的 功能 ，而不是子选择。这就是为什么您的第二个 SELECT 只是无效的语法。

Note: that's working for functions, not for sub-selects. That's why your second SELECT is simply invalid syntax.

标准SQL根本没有提供相关规定，因此该功能被某些人所皱眉，并且提供了一些干净的替代方法（由于SQL标准的改进）。它在很大程度上被Postgres 9.3+中的 LATERAL 功能所取代：

Standard SQL does not have a provision for that at all, so the feature is frowned upon by some and clean alternatives have been provided (thanks to improvements in the SQL standard). It is largely superseded by the LATERAL feature in Postgres 9.3+:

• 横向和横向之间有什么区别PostgreSQL中的子查询？

• What is the difference between LATERAL and a subquery in PostgreSQL?

简单形式可以替换为：

SELECT g
FROM   generate_series(1,10) g;

只要有可能，请将SRF移至 FROM 子句并将它们像表一样对待-从9.3版开始，几乎总是可以的。

Whenever possible move SRF to the FROM clause and treat them like tables - since version 9.3 that's almost always possible.

请注意， g 用作表和列别名是自动的。在 SELECT g 中的 g 首先绑定到列名。更明确的语法：

Note that g serves as table and column alias automatically in the example. g in SELECT g binds to a column name first. More explicit syntax:

SELECT g
FROM   generate_series(1,10) AS t(g);  -- table_alias(column_alias)

您需要了解行，一组行（〜一个表）和一个数组。这会给你一个整数的数组：

You need to understand the difference between a row, a set of rows (~ a table) and an array. This would give you an array of integer:

SELECT ARRAY(SELECT g FROM generate_series(1,10) g) AS g_arr;

浏览标签 generate-series 和 set-returning-functions 以获得许多与示例代码相关的答案。

Browse the tags generate-series and set-returning-functions for many related answers with code examples.

这篇关于如何转换实体以在PostgreSQL中设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！