create function repp(nowchar text, afterchar text) returns SETOF text
language plpgsql
as
$$
DECLARE r TEXT;
DECLARE res TEXT :='';
BEGIN
foreach r in ARRAY (SELECT ARRAY(SELECT column_name FROM information_schema.COLUMNS WHERE table_catalog='work' AND table_schema='public' AND table_name='ICD9'))
LOOP
EXECUTE 'update "ICD9" set "'||r||'" = replace("ICD9"."'||r||'",'''||nowchar||''','''||afterchar||''')';
res := res||r||',';
END LOOP;
RETURN NEXT res;
END;
$$;
alter function repp(text, text) owner to postgres;
nowchar是被替换的关键字,afterchar是要替换进去的关键字,通过information_schema获得表的所有字段,r这里是列名,因为列名是双引号的,需要转义,execute可以执行sql语句