0

In concept this is simple. I'm constructing a new database. I have all of the tables I want to create in tb.table_build and I want to write a statement that creates new tables for each of the items in the table_build select query.

DO $$
DECLARE counterVariable integer;
        CounterMax Varchar(150);
begin
  for Countermax in (select table_name 
                     from table_build 
                     where build_type = 'Number')
  loop
    CREATE TABLE Countermax (
    LIKE template_text_input);
  end loop;
end;
$$;

I cannot pass the variable CounterMax into the Create Table statement.

1
  • 1
    You need dynamic SQL
    – user1822
    Apr 29, 2022 at 5:20

1 Answer 1

0

demo

create or replace procedure create_table()
as
$$
    declare  _sql text; _row_record record;
    begin
    for _row_record in
        select table_name from table_build where build_type = 'Number' loop
    _sql :=  format ('create table %s (like template_text_input INCLUDING ALL) ',_row_record.table_name );
    raise notice '%', _sql;
    execute _sql;
    end loop;
    end
$$
language plpgsql;

then call it:

call create_table();

1
  • Worked Perfectly!
    – J Taylor
    May 9, 2022 at 2:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.