执行动态交叉表查询

Question

我在我的Postgres数据库中实现了这个函数：http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/ 以下是该函数的代码：

create or replace function xtab (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar) returns varchar language plpgsql as $$
declare
    dynsql1 varchar;
    dynsql2 varchar;
    columnlist varchar;
begin
    -- 1. retrieve list of column names.
    dynsql1 = 'select string_agg(distinct '||colc||'||'' '||celldatatype||''','','' order by '||colc||'||'' '||celldatatype||''') from '||tablename||';';
    execute dynsql1 into columnlist;
    -- 2. set up the crosstab query
    dynsql2 = 'select * from crosstab (
 ''select '||rowc||','||colc||','||cellc||' from '||tablename||' group by 1,2 order by 1,2'',
 ''select distinct '||colc||' from '||tablename||' order by 1''
 )
 as ct (
 '||rowc||' varchar,'||columnlist||'
 );';
    return dynsql2;
end
$$;

现在我可以调用这个函数：

select xtab('globalpayments','month','currency','(sum(total_fees)/sum(txn_amount)*100)::decimal(48,2)','text');

这将返回（因为函数的返回类型是varchar）：

select * from crosstab (
   'select month,currency,(sum(total_fees)/sum(txn_amount)*100)::decimal(48,2)
    from globalpayments
    group by 1,2
    order by 1,2'
 , 'select distinct currency
    from globalpayments
    order by 1'
   )  as ct ( month varchar,CAD text,EUR text,GBP text,USD text );

我该如何让这个函数不仅生成动态交叉表的代码，还能执行结果？也就是说，当我手动复制/粘贴/执行时得到的结果是这样的。但我希望它能在没有额外步骤的情况下执行：函数应该组装动态查询并执行它：

编辑1

这个函数接近了我的需求，但我需要它返回的不止是第一行记录的第一列。

摘自：有没有办法在PostgreSQL中字符串值内执行查询（类似于eval）？

create or replace function eval( sql  text ) returns text as $$
declare
  as_txt  text;
begin
  if  sql is null  then  return null ;  end if ;
  execute  sql  into  as_txt ;
  return  as_txt ;
end;
$$ language plpgsql

用法：select * from eval($$select * from analytics limit 1$$)

然而，它只返回第一行的第一列：

eval
----
2015

当实际结果看起来像这样：

Year, Month, Date, TPV_USD
---- ----- ------ --------
2016, 3, 2016-03-31, 100000

- skilbjo

---

你是否在寻找类似于EXECUTE IMMEDIATE的东西？ - Glenn

可能，但我该如何将其插入到现有函数中？ - skilbjo

此外，从我得到灵感的网站上，他说：“您无法自动执行生成的交叉表查询，因为返回表格（setof记录函数）的PL/pgSQL函数无法在运行时确定列数和类型。”- 你能使用EXECUTE IMMEDIATE来消除这个障碍吗？例如execute immediate 'select 1'而不会出错... :( - skilbjo

您忘记提供您的Postgres版本。您期望的结果与当前查询相矛盾，其中货币列按升序排列。此外，准备查询并不能解决问题。您仍然需要两次往返到DB服务器：1. 准备，2. 执行/检索返回的行。在ecpg中提到的EXECUTE IMMEDIATE不会返回行，并且不适用于您的问题，因为您显然想要检索结果行。 - Erwin Brandstetter

@cur4so 是的，行数和列数都是动态的。如果行数是固定的，我能否实现动态交叉表（只有在运行时才知道列数）？ - skilbjo

Answer 1

您所要求的是不可能的。SQL是一种严格类型的语言。PostgreSQL函数需要在创建时声明返回类型（RETURNS ..）。

有一个有限的解决方法是使用多态函数。如果您可以在调用函数时提供返回类型。但这并不明显从您的问题中。

您可以使用匿名记录返回完全动态的结果。但是，您需要在每次调用时提供列定义列表。那么您如何了解返回的列呢？进退两难。

• 重构PL/pgSQL函数以返回各种SELECT查询的输出

根据您的需求和操作方式，有各种解决方法。由于您的所有数据列似乎共享相同的数据类型，建议返回一个 数组： text[]。或者您可以返回类似于hstore或json的文档类型。相关：

• 使用CASE和GROUP BY进行动态透视的替代方法

• 将hstore键动态转换为未知键集的列

但是，更简单的方法可能只需要两个调用：1：让Postgres构建查询。 2：执行并检索返回的行。

• 使用单个SQL语句选择多个max()值

---

我不建议使用Eric Minikel在你的问题中提出的函数。完全。它不能安全地防止通过恶意格式化标识符的SQL注入攻击。除非你运行的是早于Postgres 9.1版本的过时版本，否则请使用format()来构建查询字符串。

更短、更简洁的实现可能看起来像这样:

CREATE OR REPLACE FUNCTION xtab(_tbl regclass, _row text, _cat text
                              , _expr text  -- still vulnerable to SQL injection!
                              , _type regtype)
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE
   _cat_list text;
   _col_list text;
BEGIN

-- generate categories for xtab param and col definition list    
EXECUTE format(
 $$SELECT string_agg(quote_literal(x.cat), '), (')
        , string_agg(quote_ident  (x.cat), %L)
   FROM  (SELECT DISTINCT %I AS cat FROM %s ORDER BY 1) x$$
 , ' ' || _type || ', ', _cat, _tbl)
INTO  _cat_list, _col_list;

-- generate query string
RETURN format(
'SELECT * FROM crosstab(
   $q$SELECT %I, %I, %s
      FROM   %I
      GROUP  BY 1, 2  -- only works if the 3rd column is an aggregate expression
      ORDER  BY 1, 2$q$
 , $c$VALUES (%5$s)$c$
   ) ct(%1$I text, %6$s %7$s)'
, _row, _cat, _expr  -- expr must be an aggregate expression!
, _tbl, _cat_list, _col_list, _type);

END
$func$;

与您原来的版本相同的函数调用。函数crosstab()由附加模块tablefunc提供，必须安装该模块。基础知识：

• PostgreSQL Crosstab Query

这样可以安全地处理列和表名。请注意对象标识符类型regclass和regtype的使用。也适用于架构限定名称。

• Table name as a PostgreSQL function parameter

然而，当您将字符串作为表达式传递时（_expr - 您原始查询中的cellc），它并不完全安全。这种输入本质上不安全，容易受到SQL注入攻击，并且永远不应该向公众公开。

• Postgres函数中的SQL注入与预处理查询

对于类别列表的两个列表，只扫描表格一次，速度应该会更快一些。

仍然无法返回完全动态的行类型，因为这是严格不可能的。

- Erwin Brandstetter

---

感谢您的回答 - 我还在努力理解。无需担心 SQL 注入 - 这只是针对我自己的数据库进行数据分析 - 没有其他人使用。您确定没有办法模仿 MSSQL 实现的任何动态交叉表查询功能吗？这需要字符串插值和对字符串的 exec() 调用。 - skilbjo

@JohnSkilbeck：我添加了一个使用format()函数的实现。 - Erwin Brandstetter

@Wondie：像运行其他查询一样运行它。必须安装tablefunc模块（这对OP来说是明确的）。我在上面添加了指针。 - Erwin Brandstetter

@ErwinBrandstetter 感谢您进一步的解释。这意味着我只需复制粘贴它并运行查询吗？ - wondim

@wondie：是的，例如。第二轮往返到服务器。 - Erwin Brandstetter

Answer 2

虽然不太可能，但您仍然可以执行它（从查询中执行字符串并返回SETOF RECORD）。

然后您需要指定返回记录的格式。在这种情况下，原因是规划器在做出某些决策之前需要知道返回格式（例如，在使用材质化时需要知道）。

因此，在这种情况下，您将执行查询，返回行并返回SETOF RECORD。

例如，我们可以使用包装函数来执行类似的操作，但相同的逻辑也可以折叠到您的函数中：

CREATE OR REPLACE FUNCTION crosstab_wrapper
(tablename varchar, rowc varchar, colc varchar, 
 cellc varchar, celldatatype varchar) 
returns setof record language plpgsql as $$
    DECLARE outrow record;
    BEGIN
       FOR outrow IN EXECUTE xtab($1, $2, $3, $4, $5)
       LOOP
           RETURN NEXT outrow
       END LOOP;
    END;
 $$;

然后，您在调用函数时提供记录结构，就像使用crosstab一样。 然后，当您调用查询时，您必须提供一个记录结构（如(col1类型，col2类型等），就像使用connectby一样。

- Chris Travers

---

谢谢您的回复。您能提供一个示例吗？可以使用我提供的示例，或者您自己的示例都可以。 - skilbjo

我在我的回答中称之为“进退两难”。如果要为完全动态的行类型“提供记录结构”（列定义列表），该怎么办？您必须在函数调用之前就知道它，这本身就不是完全动态的 - 如果您已经知道返回类型，那么还有更优雅的解决方案。 - Erwin Brandstetter

@Chris，我从那段代码中收到了这个错误：ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function crosstab_wrapper(character varying,character varying,character varying,character varying,character varying) line 6 at RETURN NEXT Query failed PostgreSQL said: set-valued function called in context that cannot accept a set。 - skilbjo

select * from my_function(...) as r (...) - Chris Travers