How to use dynamic column names in an UPDATE statement in a function?

Question

I want to update all the text columns by fetching the column names on runtime within the loop.

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;

$$;

All the update statements above gives error below as it is working on runtime and can not figure out the value in "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

The question below seems to be near to what my problem is but I don't know how to use it in my case

How to use dynamic column names in an UPDATE or SELECT statement in a function?

@wildplasser I don't want to rename the columns, just have to update rows in each column which is of text data type — Kiran_t, May 28 '21 at 08:21
The question you linked to will lead you in the right direction. For dynamic SQL you'll need the `EXECUTE xyz` statement, where xyz is the query you constructed. — wildplasser, May 28 '21 at 08:27

score 1 · Accepted Answer · answered May 31 '21 at 09:18

Some dynamic SQL will help. The select and update queries are taken from the question w/o change.

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;
$$;

It might be worth running a VACCUM on the target table after such a massive UPDATE.

How to use dynamic column names in an UPDATE statement in a function?

1 Answers1