PostgreSQL: Replace String data in all the Columns of a Table

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a custom function which will replace the String in all the column of a PostgreSQL Table.

Postgres provides the string replace function, but at a time you can replace string for a particular column.

Recently, I got the requirement like replace same string value in multiple columns.
So, I prepared a user-defined function which I am sharing here.

Please check code, and try it yourself:

Create a sample table with data:

create table tbl_str (t1 character varying,t2 character varying);

insert into tbl_str values

('AA','BB'),('BB','DD')

,('CC','BB'),('FF','GG');

Create a function for replacing string in all columns:

create or replace function fn_replace_string_for_all_columns(tablename text,oldvalue text,newvalue text)

returns void as

declare

eachrw record;

begin

for eachrw in

select 'UPDATE '||$1||' SET '||C.COLUMN_NAME||' = REPLACE ('||C.COLUMN_NAME||','''||$2||''','''||$3||'''); ' SQLQuery

FROM (select column_name from information_schema.columns where table_schema='public' and table_name =$1)c

loop

EXECUTE eachrw.SQLQuery;

end loop;

end;

$$language plpgsql;

Call the function:

1	select *from fn_replace_string_for_all_columns('tbl_str', 'BB', 'ZZ');

Check the result: ‘BB’ replaced by ‘ZZ’

select *from tbl_str

t1 | t2

-----------

AA | ZZ

ZZ | DD

CC | ZZ

FF | GG

Dec 4, 2017Anvesh Patel