PostgreSQL : How to run ALTER queries returned as a result from SQL SELECT statement

user_14860242 0
Viewed 462

I want to add a new column to all tables with table name pattern table_<>_details.

I use this query :

 select 'alter table ' || table_name || ' ADD COLUMN CREATED TIMESTAMP;'
 from information_schema.tables
 where table_name like 'table_%_details';

to generate the DDL queries which looks like :

alter table table_1_details ADD COLUMN CREATED TIMESTAMP;
alter table table_2_details ADD COLUMN CREATED TIMESTAMP;
alter table table_3_details ADD COLUMN CREATED TIMESTAMP;
alter table table_4_details ADD COLUMN CREATED TIMESTAMP;
alter table table_5_details ADD COLUMN CREATED TIMESTAMP;
alter table table_6_details ADD COLUMN CREATED TIMESTAMP;
alter table table_7_details ADD COLUMN CREATED TIMESTAMP;

I tried to loop through these records using the following script :

do $$ declare c_query cursor for
select
    'alter table ' || table_name || ' ADD COLUMN CREATED TIMESTAMP;'
from
    information_schema.tables
where
    table_name like 'table_%_details';

begin
 for rec in c_query loop
 execute rec;
end loop;

close c_query;
end $$

I have tried to fine tune this script but with no success, I'm getting the following error:

SQL Error [42601]: ERROR: syntax error at or near ""alter table table_1_details ADD COLUMN CREATED TIMESTAMP;""
  Where: PL/pgSQL function inline_code_block line 13 at EXECUTE statement

my question is how to modify this scrip to loop through all these results and apply the DDL to database , note (I do not want to create functions).

please any Ideas will be appreciated.

dynamic-sql plpgsql ddl postgresql
1 Answers

Just loop over the resultset of infomation_schema.tables and then use EXECUTE with your concatenated ALTER TABLE statements

DO $$
DECLARE
  row record;
BEGIN
  FOR row IN SELECT table_name FROM information_schema.tables
             WHERE table_name LIKE 'table_%_details' LOOP
    EXECUTE 'ALTER TABLE ' || row.table_name || ' ADD COLUMN CREATED TIMESTAMP;';
  END LOOP;
END;
$$;

EDIT: Alternatively you can use FORMAT to concatenate your strings instead of using ||, as pointed out by @a_horse_with_no_name

EXECUTE FORMAT('ALTER TABLE %I ADD COLUMN CREATED TIMESTAMP;',row.table_name);

Check this db<>fiddle

user_22753880
Related