Postgres-11 :动态更改表

Question

我正在尝试动态更改基于另一个表的表。下面是我用postgresql存储过程编写的代码。但是遇到了一些语法错误。请帮帮我。

我刚开始在postgresql中工作，我来自sql服务器背景。就像我们在sql server中如何打印stmt在过程中调试动态查询一样；我们有什么链接可以参考吗?请也分享一下。这会对我有帮助。

DROP TABLE IF EXISTS temp_table;
    CREATE TEMP TABLE temp_table AS
    with cte as
    (
        select column_name,data_type,character_maximum_length
        from information_schema."columns" c 
        where table_name = 'customer_new' and table_schema = 'public'
        and column_default is null
    )
    ,cte1 as
    (
        select column_name,data_type,character_maximum_length
        from information_schema."columns" c 
        where table_name = 'customer_old' and table_schema = 'public'
        and column_default is null
    )
    select cte.column_name,
    case when cte.character_maximum_length is not null then cte.data_type||'('||cte.character_maximum_length||')' else cte.data_type end as data_type
    from cte
    left join cte1 on cte.column_name = cte1.column_name
    where cte1.column_name is null;
    
    for v_column_name,v_data_type in SELECT column_name,data_type FROM temp_table
    loop
        execute format ('alter table %s add column  %s  %s  ;', v_dump_table_name, v_column_name, v_data_type);
    end loop;

    DROP TABLE IF EXISTS temp_table;

提前谢谢。

Answer 1

DROP TABLE IF EXISTS temp_table;
    CREATE TEMP TABLE temp_table AS
    with cte as
    (
        select column_name,data_type,character_maximum_length
        from information_schema."columns" c 
        where table_name = 'customer_new' and table_schema = 'public'
        and column_default is null
    )
    ,cte1 as
    (
        select column_name,data_type,character_maximum_length
        from information_schema."columns" c 
        where table_name = 'customer_old' and table_schema = 'public'
        and column_default is null
    )
    select 'alter table '||v_dump_table_name||' add column '||cte2.column_name||' '||data_type 
    as col
    from cte2;

    for v_column in SELECT col FROM temp_table
    loop
        execute format ('%s  ;', v_column);
    end loop;

    DROP TABLE IF EXISTS temp_table;