Database Reference
In-Depth Information
JOIN pg_namespace n
ON c.relnamespace = n.oid
WHERE n.nspname = 'test';
Once we've generated the script, we can just check if it all 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;
and then run the script and watch the results (success!).
postgres=# \i multi.sql
ALTER TABLE
ALTER TABLE
ALTER TABLE
How it works...
Overall, this is just an example of dynamic scripting, and has been used by DBAs for many
decades, even before PostgreSQL was born.
It can go wrong in various ways, especially if you generate SQL text with syntax errors. Just fix
that, and carry on.
The \t command means "tuples only", so \t on will ensure there are no headers, command
tags, or row counts following results.
The \o command redirects output to a file.
\! runs operating system commands, so \! cat will show the file contents on *nix systems.
The \i command redirects input from a file, or in simpler terms, will execute the named
file. Running the script in this way will probably ignore earlier recipes, so I still recommend
following those earlier guidelines.
Dynamic scripting might also be named a quick-and-dirty approach. The previous scripts didn't
filter out views and other objects in the test schema, so you'd need to add that yourself, or not,
as required.
 
Search WWH ::




Custom Search