时间戳

首页 > 解决方案 > 如何在函数的 UPDATE 语句中使用动态列名？

postgresql - 如何在函数的 UPDATE 语句中使用动态列名？

问题描述

我想通过在循环中的运行时获取列名来更新所有文本列。

drop function if exists aa.clean_data();
create or replace function aa.clean_data()
returns void
language plpgsql
as
$$
declare
    col_name varchar(50);
begin
    for col_name in select column_name from information_schema.columns where table_schema = 'aa' and table_name = 'test' and data_type in ('character varying', 'character','text', '"char"', 'name') loop
        
        update autopipe.test set col_name = initcap(col_name); -- case correction
        
        update autopipe.test set col_name = REGEXP_REPLACE(col_name, '[^a-zA-Z\d\s:/\//]', '', 'g'); -- non alpha-numeric characters
            
        update autopipe.test set col_name = REGEXP_REPLACE(col_name, '\s+$', '', 'g'); -- Trim padding
        
    end loop;

end;

$$;

上面的所有更新语句都在下面给出错误，因为它在运行时工作并且无法找出“col_name”中的值

SQL Error [42703]: ERROR: column "col_name" of relation "test" does not exist
  Where: PL/pgSQL function autopipe.clean_data() line 7 at SQL statement

下面的问题似乎与我的问题很接近，但我不知道如何在我的情况下使用它

如何在函数的 UPDATE 或 SELECT 语句中使用动态列名？

标签： postgresqlplpgsqldynamic-sql

解决方案

一些动态 SQL 会有所帮助。select和update查询取自没有变化的问题。

drop function if exists aa.clean_data();
create or replace function aa.clean_data() returns void language plpgsql as
$$
declare
    UPDATE_A constant text := $dynsql$ UPDATE autopipe.test SET %I = initcap(%I) $dynsql$;
    UPDATE_B constant text := $dynsql$ UPDATE autopipe.test SET %I = REGEXP_REPLACE(%I, '[^a-zA-Z\d\s:/\//]', '', 'g') $dynsql$;
    UPDATE_C constant text := $dynsql$ UPDATE autopipe.test SET %I = REGEXP_REPLACE(%I, '\s+$', '', 'g') $dynsql$;
    col_name text;
begin
    for col_name in select column_name from information_schema.columns where table_schema = 'aa' and table_name = 'test' and data_type in ('character varying', 'character','text', '"char"', 'name') loop
        execute format(UPDATE_A, col_name, col_name);
        execute format(UPDATE_B, col_name, col_name);
        execute format(UPDATE_C, col_name, col_name);
    end loop;
end;
$$;

VACCUM在如此庞大的UPDATE.

推荐阅读