dynamic sql to rename columns, which contain string in name

Question

I am trying to rename all columns in a table, which contain the string survey_ by simply removing this string from the column name. I wish to do this with dynamic SQL. I've formulated the following query, which runs without throwing an error, but nothing happens, vis-a-vis, no column names get renamed and I don't understand why. Can someone assist me:

do
$$
declare
  l_rec record;
begin
  for l_rec in (select table_schema, table_name, column_name 
                from information_schema.columns 
                where table_schema = 'LUCAS_LF2022' 
                and table_name = 'l2022_survey_lf_tst_pkg'
                and column_name like 'survey%') loop
     execute format ('alter table %I.%I rename column %I to REPLACE(%I, ''survey'', '''')', l_rec.table_schema, l_rec.table_name, l_rec.column_name, l_rec.column_name);
  end loop;
end;
$$

Answer 1

You don't need to use the REPLACE function directly on %I. Instead, you should construct the new column name in the format string itself.

DO
$$
DECLARE
  l_rec RECORD;
BEGIN
  FOR l_rec IN (
    SELECT table_schema, table_name, column_name
    FROM information_schema.columns
    WHERE table_schema = 'LUCAS_LF2022'
    AND table_name = 'l2022_survey_lf_tst_pkg'
    AND column_name LIKE 'survey_%'
  ) LOOP
     EXECUTE format(
       'ALTER TABLE %I.%I RENAME COLUMN %I TO %I',
       l_rec.table_schema,
       l_rec.table_name,
       l_rec.column_name,
       REPLACE(l_rec.column_name, 'survey_', '')
     );
  END LOOP;
END;
$$