Database Reference
In-Depth Information
Getting ready
Let's just create a basic schema to run some examples on the following:
postgres=# create schema test;
CREATE SCHEMA
postgres=# create table test.a (col1 INTEGER);
CREATE TABLE
postgres=# create table test.b (col1 INTEGER);
CREATE TABLE
postgres=# create table test.c (col1 INTEGER);
CREATE TABLE
How to do it...
Our task is to run an SQL statement using this form, with
X
as the tablename, against each of
our three test tables:
ALTER TABLE X
ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE;
Our starting point is a script that lists the tables 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';
relname
---------
a
b
c
(3 rows)
We then use the preceding SQL to generate the text for an SQL script, substituting the schema
name and tablename into 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
