How to parse an UPDATE statement individually through all columns of a table?

Question

I have an working UPDATE statement that must be passed through 220 columns (type text) to apply a nested CASE operator.

    UPDATE TabName
    SET
        ColName = CASE ColName
            WHEN 'Strongly disagree' THEN '1'
            WHEN 'Disagree' THEN '2'
            WHEN 'Indifferent' THEN '3'
            WHEN 'Agree' THEN '4'
            WHEN 'Strongly agree' THEN '5'
            WHEN '#NULL!' THEN NULL
            WHEN '' THEN NULL
            ELSE ColName
        END
    WHERE ColName IS NOT NULL;

When TabName and ColName are manually replaced on this code by valid table_name and column_name the update statement work as intended. All matching values are replaced by respective new values and all non matching values are maintained.

So far so good.
The challenge is to parse automatically this statement through all columns of a table.

I was able to list all columns names of the referred table with the code below, but I have tried with no success to write a functional FOR LOOP code with an UPDATE statement.

    DO
    $$
    DECLARE ColName text;
    BEGIN 
        FOR ColName IN 
            (SELECT column_name  
            FROM information_schema.columns 
            WHERE table_schema = 'public' AND table_name = 'TabName')
        LOOP
            RAISE NOTICE '%', ColName;  
        END LOOP;
    END;
    $$;

At first I tried a prepared statement but discovered that PREPARE/EXECUTE statements does not accept schema qualified names (table_name, column_name) as parameters.

Then I tried to create a plpgsql user-defined function but got stucked on parameters and CASE errors.

Any help or direction to a solution is greatly appreciated.
If needed I am available to share the table source code and a CSV file with sample data for tinkering.

Thanks.

Answer 1

To make the dynamic SQL very simple, recommended create additional function for changing texts.

CREATE OR REPLACE FUNCTION change_text(txt text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
declare 
    p_text text;
begin
    select 
    CASE txt
        WHEN 'Strongly disagree' THEN '1'
        WHEN 'Disagree' THEN '2'
        WHEN 'Indifferent' THEN '3'
        WHEN 'Agree' THEN '4'
        WHEN 'Strongly agree' THEN '5'
        WHEN '#NULL!' THEN NULL
        WHEN '' THEN NULL
        ELSE txt
    end into p_text;
return p_text;

end;
$function$
;

After then we can create function for dynamically update table. (Only for string fields)

CREATE OR REPLACE FUNCTION update_table(p_schema text, p_table text)
RETURNS bool
LANGUAGE plpgsql
AS $function$
declare 
    col_name text;
    v_sql text;
    v_seperate text;
begin

    v_seperate = '';    
    v_sql = 'update ' || p_schema || '.' || p_table || ' set ';
        
    FOR col_name IN 
        (SELECT column_name   
        FROM information_schema.columns 
        WHERE 
            table_schema = p_schema 
            AND table_name = p_table
            and data_type in ('text', 'character varying'))
    LOOP
        v_sql = v_sql || v_seperate || col_name || ' = change_text(' || col_name || ')';
        v_seperate = ',';
    END LOOP;
    
    if (v_seperate<>'') then 
        execute v_sql;
    end if;
    
    return true;

end;
$function$
;