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

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;