PostgreSQL：实现传入视图名动态关联自定义字段表的存储过程

前提准备 #

首先确保启用tablefunc扩展（用于行转列处理自定义字段）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

存储过程实现 #

以下存储过程接收视图名称参数，自动拼接视图所有字段与关联的自定义字段（将objectcustomfieldvalues中的行数据转为列）：

CREATE OR REPLACE FUNCTION get_tickets_with_custom_fields(p_view_name text)
RETURNS SETOF record AS $$
DECLARE
    v_view_columns text;
    v_custom_fields text;
    v_sql text;
BEGIN
    -- 动态获取目标视图的所有字段
    SELECT string_agg(quote_ident(column_name), ', ')
    INTO v_view_columns
    FROM information_schema.columns
    WHERE table_name = p_view_name
      AND table_schema = 'public'; -- 视图所在schema，按需调整

    -- 动态获取所有存在的自定义字段名称
    -- 若自定义字段名称存储在关联表（如customfields），请替换为对应的关联查询
    SELECT string_agg(DISTINCT quote_ident(customfield_name), ', ')
    INTO v_custom_fields
    FROM objectcustomfieldvalues;

    -- 构造动态SQL，通过crosstab将自定义字段行转列
    v_sql := format(
        'SELECT 
            v.*, 
            ct.*
         FROM %I v
         LEFT JOIN crosstab(
             ''SELECT objectid, customfield_name, value 
              FROM objectcustomfieldvalues 
              WHERE objectid IN (SELECT id FROM %I)''
         ) AS ct(objectid int, %s)
         ON v.id = ct.objectid',
        p_view_name,
        p_view_name,
        v_custom_fields
    );

    RETURN QUERY EXECUTE v_sql;
END;
$$ LANGUAGE plpgsql;

使用示例 #

调用时需指定返回结果的列结构（需匹配视图字段+自定义字段的顺序与类型）：

-- 示例：查询tickets_new视图及其关联自定义字段
SELECT * 
FROM get_tickets_with_custom_fields('tickets_new') 
AS (id int, title text, create_time timestamp, priority text, category text);

关键说明 #

1. 类型匹配：确保视图的id字段与objectcustomfieldvalues.objectid类型一致，若不一致需调整ct(objectid int,...)中的类型定义。
2. 自定义字段来源：如果自定义字段名称存储在其他表（如customfields），请修改获取v_custom_fields的SQL，例如：

   SELECT string_agg(DISTINCT quote_ident(cf.field_name), ', ')
   INTO v_custom_fields
   FROM objectcustomfieldvalues ocfv
   JOIN customfields cf ON ocfv.customfieldid = cf.id;
   

3. Schema适配：若视图不在public schema，需修改information_schema.columns查询中的table_schema值。
4. 空自定义字段处理：当无自定义字段时，存储过程仅返回视图本身的所有字段。

内容的提问来源于stack exchange，提问作者GuppyCat