postgresql - 使用 PL/pgSQL 函数将具有多个值的列动态添加到任何表

Question

我想使用函数/过程向"template"表添加一个具有多个值的附加列(例如句点名称)，并在行上做笛卡尔积，所以我的"template"与为新列提供了不同的值。

例如在我的 template_country_channel 表中添加一个包含 2 个值的周期列:

SELECT * 
FROM   unnest(ARRAY['P1', 'P2']) AS prd(period)
     , template_country_channel 
ORDER BY  period DESC
        , sort_cnty
        , sort_chan;

/* 
-- this is equivalent to: 
(
  SELECT   'P2'::text AS period
         , *
  FROM template_country_channel
) UNION ALL ( 
  SELECT   'P1'::text AS period
         , *
  FROM template_country_channel
)
--
*/

这个查询工作正常，但我想知道我是否可以把它变成一个 PL/pgSQL 函数/过程，提供要添加的新列值，要添加额外列的列(并可选择指定顺序按条件)。

我想做的是:

SELECT * 
FROM template_with_periods(
      'template_country_channel'           -- table name
    , ARRAY['P1', 'P2']                    -- values for the new column to be added
    , 'period DESC, sort_cnty, sort_chan'  -- ORDER BY string (optional)
);

并且与第一个查询有相同的结果。

所以我创建了一个像这样的函数:

CREATE OR REPLACE FUNCTION template_with_periods(template regclass, periods text[], order_by text) 
    RETURNS SETOF RECORD 
AS $BODY$

BEGIN
    RETURN QUERY EXECUTE 'SELECT * FROM unnest($2) AS prd(period), $1 ORDER BY $3' USING template, periods, order_by ;


END;
$BODY$
LANGUAGE 'plpgsql'
;

但是当我运行时:

SELECT * 
FROM template_with_periods('template_country_channel', ARRAY['P1', 'P2'], 'period DESC, sort_cnty, sort_chan');

我有错误ERROR: 42601: a column definition list is required for functions returning “record”

经过一些谷歌搜索后，我似乎需要定义列和类型的列表来执行 RETURN QUERY(正如错误消息所指出的那样)。不幸的是，整个想法是将该函数与许多"template"表一起使用，因此列名和类型列表不是固定的。

• 还有其他方法可以尝试吗？
• 或者让它工作的唯一方法是在函数中获取模板表的列名称和类型的列表？

Answer 1

如果您希望输出列列表完全动态，我用 refcursor 做了这个:

CREATE OR REPLACE FUNCTION is_record_exists(tablename character varying, columns character varying[], keepcolumns character varying[] DEFAULT NULL::character varying[])
    RETURNS SETOF refcursor AS
$BODY$

DECLARE 
    ref refcursor;
    keepColumnsList text;
    columnsList text; 
    valuesList text;
    existQuery text;
    keepQuery text;
BEGIN
    IF keepcolumns IS NOT NULL AND array_length(keepColumns, 1) > 0 THEN
        keepColumnsList :=  array_to_string(keepColumns, ', ');
    ELSE
        keepColumnsList :=  'COUNT(*)';
    END IF;

    columnsList := (SELECT array_to_string(array_agg(name || ' = ' || value), ' OR ') FROM
        (SELECT unnest(columns[1:1]) AS name, unnest(columns[2:2]) AS value) pair);

    existQuery := 'SELECT ' || keepColumnsList || ' FROM ' || tableName || ' WHERE ' || columnsList;
    RAISE NOTICE 'Exist query: %', existQuery;

    OPEN ref FOR EXECUTE
        existQuery;
    RETURN next ref;
END;$BODY$
  LANGUAGE plpgsql;

然后需要调用FETCH ALL IN得到结果。详细语法here或者那里:https://stackoverflow.com/a/12483222/630169 .似乎这是目前唯一的方法。希望在 PostgreSQL 11 中使用 PROCEDURES 有所改变。