How to do it…

Our task is to run an SQL statement using this form, with X as the table name, against each of our three test tables:

ALTER TABLE X 
ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE;

The steps are as follows:

1. Our starting point is a script that lists the tables that we want to perform tasks against—something like the following:

        postgres=# SELECT relname
              FROM pg_class c
              JOIN pg_namespace n
              ON c.relnamespace = n.oid
              WHERE n.nspname = 'test';

2. This displays the list of tables that we will act upon (so that you can check it):

         relname
        ---------
         a
         b
         c
        (3 rows)

3. We then use the preceding SQL to generate the text for a SQL script, substituting the schema name and table name in the SQL text. We then output to a script file named multi.sql, as follows:

        postgres=# \t on
        postgres=# \o multi.sql
        postgres=# SELECT 'ALTER TABLE '|| n.nspname
              || '.' || c.relname ||
        ' ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE;'
        FROM pg_class c
        JOIN pg_namespace n
        ON c.relnamespace = n.oid
        WHERE n.nspname = 'test';
        \o

4. Once we've generated the script, we can check whether all of it looks correct:

        postgres=# \! cat multi.sql
         ALTER TABLE test.a ADD COLUMN  
         last_update_timestamp TIMESTAMP WITH TIME ZONE;
         ALTER TABLE test.b ADD COLUMN 
         last_update_timestamp TIMESTAMP WITH TIME ZONE;
         ALTER TABLE test.c ADD COLUMN 
         last_update_timestamp TIMESTAMP WITH TIME ZONE;

5. Finally, we run the script and watch the results (success!):

        postgres=# \i multi.sql
        ALTER TABLE
        ALTER TABLE
        ALTER TABLE