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
and once the script is generated, you can selectively cut out the options you don't want.
SELECTarray_to_string(ARRAY(SELECT'ALTERTABLE'|| t.table_schema ||'.'|| t.table_name ||'ADDCOLUMNadd_datetimestampDEFAULT(current_timestamp);'FROM information_schema.tables t
LEFTJOIN 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 ISNULL) , '\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
||'.'|| c.table_name ||'TOhr;'FROM information_schema.columns c
WHERE c.column_name ='emp_number'), '\r')As ddlsql;
Actually thats the original way we had it, but didn't like that since it would give one row per command. For our purposes - since we are using pgAdmin a lot or dumping this in a single EXECUTE(..) call, it was easier to have a single field that has the full script rather than multiple records.
I guess you have a point though that it does make things look a little bit more complicated than they really are.