PostgreSQL中如何使用字段存储的SQL语句结果集？

在PostgreSQL 9.4的mc_preset表中，sql_rule字段存储了字符串形式的SQL语句，可通过SELECT sql_rule FROM mc_preset WHERE id_preset = 1查询得到。需要在其他SQL语句中，将该存储语句的结果集作为别名表关联查询，或作为子查询使用，且不希望使用数据库视图（因为存储的SQL可由管理员修改）。现有PL/pgSQL函数无法满足需求，需要修改函数，并了解如何在SQL中使用。若无法返回完整结果集，可接受返回指定列的数组（需支持传入列名参数）。

1. 函数编写 #

情况1：返回完整结果集 #

由于存储的SQL语句返回结构不固定，使用SETOF record作为返回类型，通过RETURN QUERY EXECUTE动态执行并返回结果：

CREATE OR REPLACE FUNCTION exec_mc_preset_sql_rule(preset_id integer)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
DECLARE
    stmt text;
BEGIN
    SELECT sql_rule INTO stmt
    FROM mc_preset
    WHERE id_preset = preset_id;

    RETURN QUERY EXECUTE stmt;
END;
$function$;

情况2：返回指定列的数组（支持传入列名） #

若只需提取某一列并返回数组，可增加列名参数，同时用format函数处理列名转义，避免SQL注入：

CREATE OR REPLACE FUNCTION exec_mc_preset_sql_rule_col(preset_id integer, col_name text)
RETURNS text[]
LANGUAGE plpgsql
AS $function$
DECLARE
    stmt text;
    result_arr text[];
BEGIN
    SELECT format('SELECT array_agg(%I) FROM (%s) AS t', col_name, sql_rule) INTO stmt
    FROM mc_preset
    WHERE id_preset = preset_id;

    EXECUTE stmt INTO result_arr;
    RETURN result_arr;
END;
$function$;

2. SQL语句中使用方法 #

使用返回完整结果集的函数 #

调用时必须明确指定返回的列名和对应数据类型，示例如下：

• 作为关联表使用：

SELECT *
FROM
    exec_mc_preset_sql_rule(1) AS table1(birthplace_id integer, birthdate date, name text)
    LEFT JOIN table2 ON table2.birthplace_id = table1.birthplace_id;

• 作为子查询使用：

SELECT *
FROM table2
WHERE birthplace_id IN (
    SELECT birthplace_id
    FROM exec_mc_preset_sql_rule(1) AS t(birthplace_id integer, birthdate date)
    WHERE birthdate < '2000-01-01'
);

使用返回指定列数组的函数 #

通过unnest将数组转换为行集，或直接用ANY操作符匹配：

-- 使用ANY操作符
SELECT *
FROM table2
WHERE birthplace_id = ANY(exec_mc_preset_sql_rule_col(1, 'birthplace_id')::integer[]);

-- 转为行集后用IN匹配
SELECT *
FROM table2
WHERE birthplace_id IN (
    SELECT unnest(exec_mc_preset_sql_rule_col(1, 'birthplace_id'))::integer
);

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