首页 文章

从返回表（或setof记录）的函数中检索列

提问于
浏览 次
1

我有这个问题的变种，并且不记得如何解决方法，只有"oop was so simple, but how to?" ...也许有一些模式和最佳方式来处理每个模式 . 让我们看看主要的一个，例如 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.

请参阅GOOD / BAD注意事项......所以， this is the problem ：一个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 side"中使用 ts_stat() ，因此，表征 the pattern ：一个返回TABLE或SETOF RECORD的函数，在我们需要列的查询中，但该函数不能在"FROM side"中 .

问题：这种模式的通用（和最优雅）解决方案是什么？如何（语法模式）显示列？

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

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 PARSER ERROR（PostgreSQL 9.5）：FROM子句中没有表"x" .

postgresql

1 回答

  • 1

    您应该 never 在 SELECT 列表中使用set-returning-function (SRF) . 主要示例应使用隐式 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时，每个生成不同数量的行都没有明显的解决方案 .

    回复于

相关问题