Redshift/PLPGSQL存储过程：如何将SHOW TABLE结果存入变量复用？

我尝试自动化生成建表脚本，编写了如下PLPGSQL存储过程：

CREATE OR REPLACE PROCEDURE talend_retail.generate_sql_create_table_by_name(
    schema_name VARCHAR(100),
    table_name VARCHAR(100),
    year INTEGER
) AS
$$
DECLARE
    query_show_table TEXT := 'SHOW TABLE my_schema.my_table' || year || ';';
    query_result_show_table TEXT;
BEGIN
    EXECUTE query_show_table INTO query_result_show_table;
    RAISE INFO 'result: %', query_result_show_table;
END
$$ LANGUAGE plpgsql;

执行时出现错误：

EXECUTE ... INTO can only be used on a SELECT query

请问如何将表结构查询结果存入变量以实现复用？

问题核心 #

1. PostgreSQL中没有SHOW TABLE命令，这是其他数据库（如MySQL）的语法，PostgreSQL需通过系统元数据表获取表结构信息。
2. EXECUTE ... INTO仅支持将SELECT查询的结果赋值给变量，无法处理非SELECT类语句的输出。

正确实现代码 #

通过查询information_schema.columns（PostgreSQL标准元数据表）获取字段定义，动态拼接建表SQL并存入变量：

CREATE OR REPLACE PROCEDURE talend_retail.generate_sql_create_table_by_name(
    schema_name VARCHAR(100),
    table_name VARCHAR(100),
    year INTEGER
) AS
$$
DECLARE
    target_table TEXT := schema_name || '.' || quote_ident(table_name || year);
    create_sql TEXT;
BEGIN
    -- 查询表结构并拼接建表语句
    SELECT 'CREATE TABLE ' || target_table || ' (' || string_agg(
        quote_ident(column_name) || ' ' || data_type || 
        CASE WHEN is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END,
        ', ' ORDER BY ordinal_position
    ) || ');' INTO create_sql
    FROM information_schema.columns
    WHERE table_schema = schema_name
      AND table_name = table_name || year;

    -- 打印结果，变量create_sql可直接复用（执行、存储等）
    RAISE INFO '生成的建表脚本: %', create_sql;
    
    -- 如需直接执行建表，取消注释以下语句
    -- EXECUTE create_sql;
END
$$ LANGUAGE plpgsql;

代码说明 #

• quote_ident()函数用于处理含特殊字符或关键字的表名/字段名，避免语法错误。
• string_agg()按字段顺序拼接定义，生成完整建表语句。
• 最终建表SQL存入create_sql变量，可直接用于后续操作。

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