PostgreSQL中如何将EXECUTE动态查询返回的行存入临时表或用作子查询

方案1：修改函数返回结果集，直接作为表使用 #

这个方案最灵活，不需要依赖临时表，调用后可以直接用于你提到的各类场景。
首先你原有函数有两处可优化的点：

1. format拼接时'set %2$I = 1', || 多了多余的逗号，会触发语法错误
2. 用正则移除set子句的逻辑容易误匹配，直接在拼接时判断即可，不需要事后正则替换

修改后的函数代码：

drop function if exists some_func;
create function some_func(table_name text, field_name text)
returns setof record as $$
declare 
    query_str text;
    set_part text;
begin
    -- 动态拼接set部分，替代正则替换，更安全
    set_part := format('%I = 1', field_name);
    if field_name != 'some value' then
        set_part := set_part || ', some_other_value = null';
    end if;

    query_str := format(
        'update %I 
        set %s
        returning id, %L as table_name, %I',
        table_name,
        set_part,
        table_name,
        field_name
    );
    -- 直接返回执行结果
    return query execute query_str;
end;
$$ language plpgsql;

调用示例 #

场景1：直接创建临时表存储结果

create temp table my_tmp_tbl on commit drop as
select * from some_func('your_table', 'your_field') 
as t(id int, table_name text, field_val int);

注意：as后面的列类型需要和你实际的id、指定字段的类型匹配，如果你指定字段是text类型就把field_val改成text即可

场景2：直接用于其他UPDATE语句

update some_other_table
set some_field = update_query.field_val
from some_func('your_table', 'your_field') 
    as update_query(id int, table_name text, field_val int)
where some_other_table.object_id = update_query.id;

方案2：在函数内部直接创建临时表 #

如果你不想每次调用都指定返回列结构，可以直接在函数内生成临时表，调用后外部会话可以直接访问该临时表：

drop function if exists some_func;
create function some_func(table_name text, field_name text, tmp_tbl_name text default 'tmp_update_result')
returns void as $$
declare 
    query_str text;
    set_part text;
begin
    set_part := format('%I = 1', field_name);
    if field_name != 'some value' then
        set_part := set_part || ', some_other_value = null';
    end if;

    query_str := format(
        'create temp table %I on commit drop as
        update %I 
        set %s
        returning id, %L as table_name, %I',
        tmp_tbl_name,
        table_name,
        set_part,
        table_name,
        field_name
    );
    execute query_str;
end;
$$ language plpgsql;

调用示例 #

-- 调用函数，默认生成名为tmp_update_result的临时表
select some_func('your_table', 'your_field');
-- 直接使用临时表
select * from tmp_update_result;
-- 也可以自定义临时表名
select some_func('your_table', 'your_field', 'my_custom_tmp');
select * from my_custom_tmp;

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