postgresql - 从返回表(或记录集)的函数中检索列

Question

我一直有这个问题的变体，不记得如何解决，只记得“oop 是如此简单，但如何做？”......也许有一些模式和处理每种模式的最佳方法。让我们看看主要的，通过 unnest() 和 ts_stat() 进行示例。

首先，很好的例子，没有问题，因为 unnest() 只返回一列:

SELECT * FROM unnest(array[1,2,3]) t(id); -- is ok, the int columns there!
SELECT unnest(array[1,2,3]) t(id); -- is ok, the int columns

WITH t AS (SELECT unnest(array[1,2,3]) as id)
 SELECT id, unnest(array[4,id]) as x
 FROM t;  -- more complex, but ok!

现在是一个返回定义的 SETOF RECORD 的函数，

SELECT * FROM ts_stat('SELECT kx FROM terms where id=2') -- GOOD
-- show all word|ndoc|nentry columns

SELECT ts_stat('SELECT kx FROM terms where id=2') as x -- BAD
-- because lost columns, show only "x" column... but works

-- NOTE: you can imagine any other function, as json_each(), etc.

查看好的/坏的注意事项...所以，这就是问题所在:一个 SETOF RECORD 多了一个列。在最简单的(上面的unnest)情况下，解决方案是在“FROM side”中使用，作为一个表；但是，当 RECORD 有多个字段时，问题就出现了。

--MAIN EXAMPLE FOR THE DISCUSSION:
WITH t AS (SELECT unnest(array[1,2,3]) as id)
 SELECT id, ts_stat('SELECT kx FROM terms where id='||id) as x
 FROM t;  -- BAD, but works...

现在，在这个主要示例中，不可能在“FROM 端”中使用 ts_stat()，因此，表征模式:一个函数在我们需要列的查询中返回 TABLE 或 SETOF RECORD，但该函数不能在“FROM 端”。

问题:这个模式的通用(也是最优雅的)解决方案是什么？如何(语法模式)显示列？

---

注意:另一个问题是，如果你不记得解决方案的确切语法，你会尝试一些不起作用的东西......在这种情况下是一个错误:

WITH t AS (SELECT unnest(array[1,2,3]) as id)
 SELECT id, x.word, x.ndoc, x.nentry 
 FROM (
      SELECT t.nsid, 
             ts_stat('SELECT kx FROM terms where id='||id) as x
      FROM t
  ) s;

SQL 解析器错误(PostgreSQL 9.5):FROM 子句中没有表“x”。

Answer 1

你不应该永远使用set-returning-function (SRF)在 SELECT 列表中。主要示例应使用隐式 LATERAL JOIN 编写:

SELECT v.id, x.*
FROM (VALUES (1),(2),(3)) v(id)
JOIN ts_stat('SELECT kx FROM terms where id=' || v.id) x ON true;

lateral join此处是隐含的，因为 SRF 可以引用在 FROM 子句之前指定的关系中的列，而无需使用关键字 LATERAL。在上面的示例中，SRF ts_stat() 横向引用了列和关系 v(id)。您也可以使用例如子查询，但随后您必须显式使用关键字 LATERAL。

请注意，虽然您可以在选择列表中使用 SRF，但不鼓励使用它。您提供了 unnest(anyarray) 的示例，这很有趣，因为还有重载变体 unnest(anyarray, ...) (即在一次调用中取消嵌套多个数组)在选择列表中使用时会抛出错误； in 只能用作行源。您不应在选择列表中使用 SRF 的原因是，当使用多个 SRF，每个 SRF 产生不同的行数时，没有明显的解决方案。