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

Question

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.

Jim Jones · Accepted Answer · 2021-03-03 15:16:26Z

3

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

edited Mar 3, 2021 at 15:16

answered Mar 3, 2021 at 15:09

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

Even better: execute format('ALTER TABLE %I ADD COLUMN CREATED TIMESTAMP;', row.table_name);

Jim Jones Over a year ago

@a_horse_with_no_name thanks for the hint, it indeed looks neater! I will add it to the answer :)

M AboEl-Soud Over a year ago

works like a charm . thanks a lot JimJones you saved my time , also thanks @a_horse_with_no_name for your hint as it looks neater!.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related