Extract specific Columns from PostgreSQL table and Do an update on its values

Question

I have a PostgreSQL database and I need to do an update over values of specific Columns. The number of columns is so big and I need to do the same operation to different table So better to extract them dynamically.

More specifically I want to extract from the table all the columns whose names ends with "_suffix" and do an update on their values. I started trying to make a script but I don't know if it is the right road!

 SELECT columns.column_name
 FROM information_schema.columns
 WHERE columns.table_name = 'myInitialTable' AND columns.column_name like '%\_suffix%' AND columns.table_schema = 'public';

I created a view of this query and I used it in the following function :

CREATE OR REPLACE FUNCTION updatetable() RETURNS int4 AS 
$BODY$
DECLARE r RECORD;
BEGIN
    FOR r IN SELECT * from v_reduced_table LOOP
    update myInitialTable 
    set r.column_name = case
            when r.column_name = '' then NULL
            when r.column_name = 'value1' or r.column_name = 'value2' then  'xxxxx'
            else r.column_name end;
END LOOP;
return 1;
END;
$BODY$
LANGUAGE plpgsql;

SELECT updatetable() as output;

this query do a loop on every column ending with suffix and updates its values. but when I run it I get

ERROR:  syntax error at or near "$1"
LINE 1: update myInitialTable set  $1  = case when  $2  = '' then NULL when  ...

Any help is appreciated :)

no to different values depending on the content ( exactly 2 values ) — Rad1
– Rad1, Commented Oct 27, 2015 at 23:13
Explain (in the question) how to distinguish the two kinds of columns. — klin
– klin, Commented Oct 28, 2015 at 0:00
like '%_suffix%' actually means contains '{any character}suffix' (not '_suffix' -- an underscore is any character). You probably want like '%\_suffix' which means "ends with" _suffix. That aside, I still don't understand what value(s) you want to update those columns with. — Hambone
– Hambone, Commented Oct 28, 2015 at 0:40
I want to reduce the table to a table having only columns ending with "_suffix" and to ignore all others columns. — Rad1
– Rad1, Commented Oct 28, 2015 at 8:11

klin · Accepted Answer · 2015-10-28 12:15:15Z

In your function you need to use dynamic commands. The funcion format() is often very helpful.

Example data:

create table my_table(col1_suffix text, col2_suffix text, col3_suffix text);
insert into my_table values ('a', 'b', 'c');

Example function:

CREATE OR REPLACE FUNCTION update_my_table() RETURNS void AS 
$BODY$
DECLARE r RECORD;
BEGIN
    FOR r IN
        SELECT columns.column_name
        FROM information_schema.columns
        WHERE columns.table_name = 'my_table' 
        AND columns.column_name like '%\_suffix%' 
        AND columns.table_schema = 'public'
    LOOP
        EXECUTE(FORMAT($f$
            UPDATE my_table
            SET %s = CASE
                WHEN '%s' = 'col1_suffix' THEN 'col1'
                WHEN '%s' = 'col2_suffix' OR '%s' = 'col3_suffix' THEN 'xxxxx'
            END;$f$, r.column_name, r.column_name, r.column_name, r.column_name));
    END LOOP;
END;
$BODY$
LANGUAGE plpgsql;

Usage:

select update_my_table();
select * from my_table;

 col1_suffix | col2_suffix | col3_suffix 
-------------+-------------+-------------
 col1        | xxxxx       | xxxxx
(1 row)

Collectives™ on Stack Overflow

Extract specific Columns from PostgreSQL table and Do an update on its values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related