PostgreSQL information_schema

PostgreSQL has a special schema named information_schema where it stores meta data about database objects (somewhat like Oracle’s DBA_* tables). If you want to iterate over all the tables in some schema and do some special processing on each of them, you can write some simple code to do it.

Here is a simple example that iterates over all the table names that are in the information_schema:

CREATE OR REPLACE FUNCTION test()
RETURNS VOID
AS $$
DECLARE
    my_row    RECORD;
BEGIN       
    FOR my_row IN 
        SELECT table_name
        FROM   information_schema.tables
        WHERE  table_schema = 'information_schema'
    LOOP
        RAISE NOTICE 'table name is %', my_row.table_name;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT test();

In similar fashion, you can iterate over other database objects like views, sequences, etc.