如何使用 pl/pgSQL 来处理'comma separated list'返回？

Question

我正在尝试将许多表UNION ALL到一个新表中。旧表的列是相同的，但列的顺序不同，所以下面的SQL语句会得到错误的结果：

CREATE TABLE sum_7_2018_xia_weijian
AS
(
        SELECT * FROM huiwen
        UNION
        SELECT * FROM penglai
        UNION
        SELECT * FROM baoluo
        UNION
        SELECT * FROM dongge
        UNION
        SELECT * FROM resultdonglu
        UNION
        SELECT * FROM resultwencheng
        UNION
        SELECT * FROM tan_illeg
);

我终于纠正了它，但 SQL 语句太冗余了：

步骤 1. 获取名为 huiwen 的旧表之一的列名

SELECT string_agg(column_name, ',')
                FROM information_schema.columns
                WHERE table_schema = 'public' AND table_name   = 'huiwen';

结果：

>                                      string_agg                                     
> ----------------------------------------------------------------------
>
>  gid,id,geom,sxm,sxdm,sxxzqdm,xzqhdm,xzmc,sfzgjsyd,sfkfbj,sfjbnt,sfld,sflyhx,sfhyhx

步骤 2. 将表合并为新表。我将表 huiwen 的string_agg复制到每个 SELECT-UNION 以保持列的顺序，这很笨拙。

CREATE TABLE sum_2018_xia_weijian
AS
(
        SELECT gid,id,geom,sxm,sxdm,sxxzqdm,xzqhdm,xzmc,sfzgjsyd,sfkfbj,sfjbnt,sfld,sflyhx,sfhyhx
        FROM huiwen
        UNION ALL
        SELECT gid,id,geom,sxm,sxdm,sxxzqdm,xzqhdm,xzmc,sfzgjsyd,sfkfbj,sfjbnt,sfld,sflyhx,sfhyhx
        FROM penglai
        UNION ALL
        SELECT gid,id,geom,sxm,sxdm,sxxzqdm,xzqhdm,xzmc,sfzgjsyd,sfkfbj,sfjbnt,sfld,sflyhx,sfhyhx
        FROM baoluo
);

结果：

> Query returned successfully: 2206 rows affected, 133 msec execution time.

我试图通过使用变量Declarations来处理列名来pl/pgSQL进行一些优化，但未能找到任何 SQL 数据类型可以处理这个问题。使用RECORD结果Pseudo-Types ERROR：

CREATE or replace FUNCTION ct() RETURNS RECORD AS $$
DECLARE
    clms RECORD;
BEGIN
    SELECT column_name INTO clms
        FROM information_schema.columns
        WHERE table_schema = 'public' AND table_name = 'huiwen';
    RETURN clms;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE sum_2018_xia_weijian
AS
(
    SELECT ct() FROM huiwen
    UNION ALL
    SELECT ct() FROM penglai
    UNION ALL
    SELECT ct() FROM baoluo
    UNION ALL
    SELECT ct() FROM dongge
    UNION ALL
    SELECT ct() FROM resultdonglu
    UNION ALL
    SELECT ct() FROM resultwencheng
    UNION ALL
    SELECT ct() FROM tan_illeg
);

Answer 1

您可以使用

STRING_AGG两次来获取UNION ALL。您可以通过在string_agg中按column_name显式排序来按特定顺序获取所有列。

这是一个泛型函数，它采用一个表数组和一个最终的表名。

CREATE or replace FUNCTION fn_create_tab(tname_arr TEXT[], p_tab_name TEXT) 
RETURNS VOID AS $$
DECLARE
l_select TEXT;
BEGIN
   select STRING_AGG(query,' UNION ALL ' ) INTO l_select
    FROM
    (
     SELECT 'select ' || string_agg( column_name,','
     ORDER BY column_name ) || ' from ' || table_name as query
       FROM information_schema.columns
      WHERE table_schema = 'public' AND table_name = ANY (tname_arr)
     GROUP BY table_name
     ) s;
 IF l_select IS NOT NULL
  THEN
    EXECUTE format ('DROP TABLE IF EXISTS %I',p_tab_name);
    EXECUTE format ('create table %I  AS %s',p_tab_name,l_select);
  END IF;    
END;
$$ LANGUAGE plpgsql;

现在，像这样运行函数：

select fn_create_tab(ARRAY['huiwen','penglai'],'sum_2018_xia_weijian');

Answer 2

与其使编程块变得复杂，不如遵循 Union 或 Union All 文档中的以下一些概念，如前所述：

1. 所有查询中的列数必须相同。
2. 相应的列必须具有兼容的数据类型。
第一个查询的列名
3. 确定组合结果集的列名。
4. GROUP BY 和 HAVING 子句应用于每个单独的查询，而不是最终的结果集。
5. ORDER BY 子句应用于组合结果集，而不是单个结果集中。

通过遵循第 3 点，使您的联合查询进行调整以引用结果中预期列顺序的表。