sql - 连接来自另一个查询的 crosstab() 查询的返回类型

Question

我不能在这里发布我的原始查询，它太复杂了。所以我用一个示例查询来解释我的问题:

工作枢轴查询:

Select pivot.* from public.crosstab($$ select 'na' as na, 1 as analysis,100 as value 
 union all select 'ba' as na, 2 as analysis,100 as value $$,
 $$ VALUES ('1'), ('2')$$
) as pivot (na text, "1" integer,"2" integer)

但我想这样使用它:

Select pivot.* from public.crosstab($$ select 'na' as na, 1 as analysis,100 as value 
 union all select 'ba' as na, 2 as analysis,100 as value $$,
 $$ VALUES ('1'), ('2')$$
) as pivot <b>select '(na text,"1" integer,"2" integer)'</b>

我想添加查询的返回类型(粗体强调)。如何做到这一点？

Answer 1

清晰的测试用例

首先我格式化并阐明了你的例子:

SELECT * FROM public.crosstab(
   $$VALUES ('na', 1, 100)   -- VALUES expression does the same, shorter
          , ('ba', 2, 300)$$ -- no column names needed (ignored anyway)
  ,$$VALUES ('1'), ('2')$$
   ) AS pivot (na text, co11 int, col2 int)  -- sane column names

输入表:

col1  key  val
---------------
na    1    100
ba    2    300

输出表

na    co11    col2
--------------------
na    100     <NULL>
ba    <NULL>  300

您可以自由选择输出列的名称，而无需拘泥于"1"、"2" 等笨拙的名称。只有数据类型必须匹配。

动态返回类型

您不能从查询中提供列定义列表。我们在 SO 上收到过很多类似的请求。它们都与 SQL 的本质相冲突，SQL 不接受“事后”的返回类型。必须与查询一起声明，至少在执行它时。

您必须使用要在第二个示例中添加的 SELECT 查询的输出来构建 crosstab() 查询。 两次 到服务器的往返。

但是我们可以从系统目录中读取熟知的类型 - 毕竟SELECT * FROM tbl 就是这样做的。我已经努力尝试并实现了我在这个相关答案中发现的内容:

• Dynamic alternative to pivot with CASE and GROUP BY

我的函数 crosstab_n() 可能是您的最佳选择。 阅读链接的答案!

它是一个围绕 crosstab() 的包装器，它接受查询字符串(就像 crosstab())和一个额外的多态参数，提供返回类型。我们仍然无法即时 传递返回类型。 SQL 唯一可接受的方式是从系统目录中读取它。因此，我们通过创建临时表来“即时”安装复合类型:

CREATE TEMP TABLE my_pivot (na text, col1 int, col2 int);

SELECT * FROM crosstab_n(
    $$VALUES ('na', 1, 100), ('ba', 2, 100)$$
    ,NULL::my_pivot
    );

结果如上。瞧!

如果您希望临时表仅针对事务持续存在，请向其中添加ON COMMIT DROP。这两个语句必须在一个事务中执行。详情:

• Select from a table variable