How to create multiple tables based on existing tables in postgreSQL?

Question

I have several tables, and I need to create a matching _hist table for each one. For example if I have:

CREATE TABLE public.test (
    first       text,
    middle      text,
    "last"      text,
    "timestamp" timestamp without time zone NOT NULL default now()
);

I would like to create:

CREATE TABLE public.test_hist (
    "action"    text,
    "user"      text,
    first       text,
    middle      text,
    "last"      text,
    "timestamp" timestamp without time zone NOT NULL default now()
);

I'm in the initial phase of the project I'm working on, and requirements keep changing.

As I run the changed script that drops and re-creates tables, I would like for _hist tables to somehow get automatically generated, so I don't need to make the same change in two places.

Is it possible to do that with SQL at the end of my table creation script?

I have postgreSQL 10.

Or should I do it in python?

Answer 1

Most of these procedural things can be done using a PL/pgSQL DO Block. So you don't have to get into another language. Assuming your main tables have a naming pattern like 'mt_%', you could do something like the following.

do
$create_hist_tables$
declare
  main_table text;
begin
for main_table in
  select
    'public.' || quote_ident(table_name)
  from
    information_schema.tables
  where
    table_name like 'mt_%'
    and table_schema = 'public'
loop
  execute 'CREATE TABLE ' || main_table || '_hist ("action" text, "user" text, like ' || main_table || ' including all)';
end loop;
end
$create_hist_tables$
;

Building a long query using concatenation will not be neat for maintainability. So you could replace the single create query with a series of create and alter commands within the loop.

I have tried this on PG 11.4.