DML to generate DDL and DCL- Making structural and Permission changes to multiple tables

Every once in a while you are tasked with an issue such as having to create logging fields in each of your tables or having to put like constraints on each of your tables or you need to Grant an X group or X user rights to a certain set of tables.

The nice thing about having an information_schema is that it provides an easy way to generate scripts to do just that with plain SELECT statements. In PostgreSQL its even easier with the array_to_string functions and ARRAY functions, you can get the script in a single field result. In the following sections we'll demonstrate some examples of this.

Data Definition Language (DDL): How to add an Added Date timestamp to all your tables that don't have them

For example you may decide one day it would be nice to have a date time stamp on all your table records, but you don't want to add these fields to tables that already have them and you are way too lazy to go thru each and every table with PgAdmin etc. to do this. The below code will generate a script to add these fields to all tables in a schema called hr that don't have the add_date field already and once the script is generated, you can selectively cut out the options you don't want.

SELECT array_to_string(ARRAY(SELECT 'ALTER TABLE ' || t.table_schema || '.' 
    || t.table_name || ' ADD COLUMN add_date timestamp DEFAULT(current_timestamp);' 
    FROM information_schema.tables t 
        LEFT JOIN information_schema.columns c 
        ON (t.table_name = c.table_name AND 
            t.table_schema = c.table_schema AND c.column_name = 'add_date') 
    WHERE t.table_schema = 'hr' AND c.table_name IS NULL) , '\r') As ddlsql  

Data Control Language (DCL): Granting Table Permissions to Groups

This technique comes in pretty handy for granting permissions to groups and users based on some somewhat arbitrary requirement. Here is an example of that. The below SELECT statement will generate a script that gives read rights to group HR for all tables across all schemas that have a field called emp_number

SELECT array_to_string(ARRAY(SELECT 'GRANT SELECT ON TABLE ' || c.table_schema 
    || '.' || c.table_name || ' TO hr;' 
    FROM information_schema.columns c 
    WHERE c.column_name = 'emp_number'), '\r') As ddlsql;