To be able to use NHibernate – and as a general purpose and learning exercise – I wanted to write a script that adds a version column of type integer to all tables in the database, unless such column exists (in Rails the schema_migrations table has a version column for example).
In SQL Server we have the “secret” sproc sp_MSforeachtable for this purpose, but how to bend the stricter syntax of plpgslq to achieve something similar? After a number of hours of trial and error, here’s is a script that actually appears to work, at least with PostgreSQL 9.0 and probably earlier versions:
CREATE OR REPLACE FUNCTION add_version_column_to_all_tables()
RETURNS VOID
AS $$
DECLARE
my_row RECORD;
BEGIN
FOR my_row IN
SELECT table_name
FROM information_schema.tables
WHERE table_schema = ‘public’
LOOP
IF NOT EXISTS
(
SELECT attname FROM pg_attribute WHERE attrelid =
(SELECT oid FROM pg_class WHERE relname = my_row.table_name )
AND attname = ‘version’
)
THEN
EXECUTE(’ALTER TABLE ‘ || my_row.table_name || ‘ ADD COLUMN version int NOT NULL DEFAULT 0;’);
END IF;
END LOOP;
END
$$
LANGUAGE plpgsql;
SELECT add_version_column_to_all_tables();
Im sure this script can be optimized, please post any improvements in the columns.
Hope it helps.
Recent Comments