使用PL/pgSQL函数将具有多个值的列动态添加到任何表 [英] Dynamically add a column with multiple values to any table using a PL/pgSQL function

问题描述

我想使用函数/过程将具有多个值的附加列(例如，期间名称)添加到模板"表中，并对行进行笛卡尔积，因此我的模板"与为新列提供了不同的值.

I would like to use a function/procedure to add to a 'template' table an additional column (e.g. period name) with multiple values, and do a cartesian product on the rows, so my 'template' is duplicated with the different values provided for the new column.

例如在我的template_country_channel表中添加一个带有2个值的期间列:

E.g. Add a period column with 2 values to my template_country_channel table:

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函数/过程，提供要添加的新列值，将该列添加额外的列(并可以选择指定顺序)根据条件).

This query is working fine, but I was wondering if I could turn that into a PL/pgSQL function/procedure, providing the new column values to be added, the column to add the extra column to (and optionally specify the order by conditions).

我想做的是:

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)
);

并具有与第一个查询相同的结果.

and have the same result as the 1st query.

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

So I created a function like:

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(如错误消息所精确指出的那样). 不幸的是，整个想法是将函数与许多模板"表一起使用，因此列名&类型列表不是固定的.

After some googling, it seems that I need to define the list of columns and types to perform the RETURN QUERY (as the error message precisely states). Unfortunately, the whole idea is to use the function with many 'template' tables, so columns name & type lists is not fixed.

• 还有其他尝试方法吗?
• 或者使它起作用的唯一方法是将其包含在函数中，这是一种获取列名称和template表类型的列表的方法吗?

• Is there any other approach to try?
• Or is the only way to make it work is to have within the function, a way to get list of columns' names and types of the template table?

推荐答案

如果您希望输出列列表完全动态，则使用refcursor完成此操作:

I did this with refcursor if You want output columns list completely dynamic:

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以获得结果.详细语法此处或那里:

Then need to call FETCH ALL IN to get results. Detailed syntax here or there: https://stackoverflow.com/a/12483222/630169. Seems it is the only way for now. Hope something will be changed in PostgreSQL 11 with PROCEDURES.

这篇关于使用PL/pgSQL函数将具有多个值的列动态添加到任何表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！