Re: alter multiple tables

On Mar 16, 12:08 pm, t(dot)(dot)(dot)(at)wildenhain(dot)de (Tino Wildenhain) wrote:
> > Since I have a lot of tables and mirrored backups, I am wondering if
> > there is a way to alter automatically all tables where colname matches
> > 'username'
> > Is there a way to do this?

Enter psql with '-E' flag, and see the query that is generated when
using commands like '\dt' (which is for showing the tables in the
current database).

For example:
-bash-3.1$ psql -U some_user -d test_db -E
Welcome to psql 8.2.1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

eds_db=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
public | test | table | some_user

Use the generated query, and modify it to suit your needs. There are
ways of extracting the columns from a given table name. With that, do
a for loop.

Daniel