Wrappers around the PostgreSQL catalog, to get convenient information on all sorts of DB objects in a format that's more useful than the information schema.

pgsql-system-views.sql

--
-- Copyright (c) Denis de Bernardy, 2011-2013
-- MIT license
--

CREATE SCHEMA IF NOT EXISTS system;

CREATE OR REPLACE FUNCTION system.typeof("any")
    RETURNS regtype
AS 'pg_typeof'
LANGUAGE INTERNAL STABLE;

COMMENT ON FUNCTION system.typeof("any") IS
'Returns the input''s type.';

CREATE OR REPLACE FUNCTION system.debug(_msg text = NULL, _start timestamp = NULL)
    RETURNS timestamp
AS $$
DECLARE
    _stop       interval;
    _stamp      text;
BEGIN
    _stop := (clock_timestamp() AT TIME ZONE 'UTC' - _start)::interval(6);
    
    IF  _stop::interval(0) >= '00:01:00'
    THEN
        _stamp := _stop::interval(0);
    ELSEIF _stop >= '00:00:00.01'
    THEN
        _stamp := extract('millisecond' from _stop::interval(3)) || ' ms';
    ELSEIF _stop IS NOT NULL
    THEN
        _stamp := extract('millisecond' from _stop) || ' ms';
    END IF;
    
    IF  _msg IS NOT NULL AND _stamp IS NOT NULL
    THEN
        RAISE NOTICE '

% / %
', _msg, _stamp;
    ELSEIF _msg IS NOT NULL
    THEN
        RAISE NOTICE '

%
', _msg;
    ELSEIF _stamp IS NOT NULL
    THEN
        RAISE NOTICE '

%
', _stamp;
    END IF;
    
    RETURN clock_timestamp() AT TIME ZONE 'UTC';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION system.debug(_msg text, _start timestamp with time zone)
    RETURNS timestamp
AS $$
    SELECT  system.debug($1, ($2 at time zone 'UTC')::timestamp);
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION system.defproc(_proc regprocedure)
    RETURNS text
AS $$
DECLARE
    _def        text;
    _eol        text;
    _unindent   text;
    _untabbed   text;
    line        text;
    pos         int;
BEGIN
    _def := pg_get_functiondef($1);
    
    _eol := (regexp_matches(_def, E'\\r?\\n'))[1];
    
    _unindent := (regexp_matches(_def, _eol || E'AS\\s*\\$(?:functionx*)\\$' || _eol || E'(\\t*)'))[1];
    
    IF  _unindent <> ''
    THEN
        _def := regexp_replace(_def, '^' || _unindent, '', 'gn');
    END IF;
    
    FOR line IN
    SELECT  regexp_split_to_table(_def, _eol) as line
    LOOP
        line = trim(trailing E' \t' from line);
        
        LOOP
            pos := position(E'\t' in line);
            
            IF  pos = 0
            THEN
                EXIT;
            END IF;
            
            line := substring(line from 1 for pos - 1) ||
                    repeat(' ', 4 - (pos % 4)) ||
                    substring(line from pos + 1 for length(line));
        END LOOP;
        
        _untabbed := COALESCE(_untabbed || _eol, '') || line;
    END LOOP;
    
    RETURN _untabbed;
END;
$$ LANGUAGE plpgsql STABLE STRICT
SET search_path FROM current;

COMMENT ON FUNCTION system.procdef(regprocedure) IS
'Returns the function''s def.';

CREATE OR REPLACE FUNCTION system.delproc(_proc text)
    RETURNS SETOF text
AS $$
DECLARE
    rec         record;
BEGIN
    FOR rec IN
    SELECT  proc.proname || '(' || pg_get_function_identity_arguments(proc.oid) || ')' as proc
    FROM    pg_catalog.pg_proc as proc
    JOIN    pg_catalog.pg_namespace as nsp
    ON      nsp.oid = proc.pronamespace
    AND     nsp.nspname <> ALL('{pg_catalog, information_schema}'::name[])
    AND     nsp.nspname = ANY (current_schemas(false))
    WHERE   proc.proname LIKE _proc
    AND     pg_catalog.pg_function_is_visible(proc.oid)
    LOOP
        RETURN QUERY
        SELECT  rec.proc::text;
        
        EXECUTE $X$
        DROP FUNCTION $X$ || rec.proc || $X$ CASCADE;
        $X$;
    END LOOP;
END;
$$ LANGUAGE plpgsql STRICT
SET search_path FROM current;

CREATE OR REPLACE VIEW system.tables AS
SELECT  t.oid::regclass as relid,
        t.relname,
        tns.nspname as relnamespace,
        t.relowner,
        t.relistemp
FROM    pg_class as t
JOIN    pg_namespace as tns
ON      tns.oid = t.relnamespace
WHERE   t.relkind = 'r';

COMMENT ON VIEW system.tables IS
'Scans tables.';

CREATE OR REPLACE VIEW system.views AS
SELECT  v.oid::regclass as relid,
        v.relname,
        vns.nspname as relnamespace,
        v.relowner,
        v.relistemp
FROM    pg_class as v
JOIN    pg_namespace as vns
ON      vns.oid = v.relnamespace
WHERE   v.relkind = 'v';

COMMENT ON VIEW system.views IS
'Alternative to the information schema views.';

CREATE OR REPLACE VIEW system.columns AS
SELECT  att.attrelid::regclass,
        att.attname,
        att.atttypid::regtype,
        att.attnum,
        att.attnotnull,
        CASE
        WHEN att.atthasdef
        THEN pg_get_expr(def.adbin, def.adrelid, true)
        ELSE NULL
        END as attdef
FROM    pg_attribute as att
LEFT JOIN pg_attrdef as def
ON      def.adrelid = att.attrelid
AND     def.adnum = att.attnum
AND     att.atthasdef
WHERE   att.attnum > 0
AND     NOT att.attisdropped;

COMMENT ON VIEW system.columns IS
'Scans columns.';

CREATE OR REPLACE VIEW system.column_names As
SELECT  att.attrelid::regclass as attrelid,
        att.attname,
        att.attnum
FROM    pg_attribute as att
WHERE   att.attnum > 0
AND     NOT att.attisdropped;

COMMENT ON VIEW system.column_names IS
'Scans columns (fast).';

CREATE OR REPLACE VIEW system.constraints AS
SELECT  con.conrelid::regclass,
        con.conname,
        con.conkey,
        ARRAY(
        SELECT  att.attname
        FROM    pg_attribute as att,
                generate_subscripts(con.conkey, 1) as k
        WHERE   att.attrelid = con.conrelid
        AND     att.attnum = con.conkey[k]
        ORDER BY k
        ) as conkey_names,
        pg_get_constraintdef(con.oid, true) as condef
FROM    pg_constraint as con
WHERE   con.contype = 'c';

COMMENT ON VIEW system.constraints IS
'Scans check constraints.';

CREATE OR REPLACE VIEW system.constraint_names AS
SELECT  con.conrelid::regclass,
        con.conname,
        con.conkey
FROM    pg_constraint as con
WHERE   con.contype = 'c';

COMMENT ON VIEW system.constraint_names IS
'Scans check constraints (fast).';

CREATE OR REPLACE VIEW system.pkeys AS
SELECT  con.conrelid::regclass,
        con.conname,
        con.conkey,
        ARRAY(
        SELECT  att.attname
        FROM    pg_attribute as att,
                generate_subscripts(con.conkey, 1) as k
        WHERE   att.attrelid = con.conrelid
        AND     att.attnum = con.conkey[k]
        ORDER BY k
        ) as conkey_names
FROM    pg_constraint as con
WHERE   con.contype = 'p';

COMMENT ON VIEW system.pkeys IS
'Scans primary key constraints.';

CREATE OR REPLACE VIEW system.pkey_names AS
SELECT  con.conrelid::regclass,
        con.conname,
        con.conkey
FROM    pg_constraint as con
WHERE   con.contype = 'p';

COMMENT ON VIEW system.pkey_names IS
'Scans primary key constraints (fast).';

CREATE OR REPLACE VIEW system.ukeys AS
SELECT  con.conrelid::regclass,
        con.conname,
        con.conkey,
        ARRAY(
        SELECT  att.attname
        FROM    pg_attribute as att,
                generate_subscripts(con.conkey, 1) as k
        WHERE   att.attrelid = con.conrelid
        AND     att.attnum = con.conkey[k]
        ORDER BY k
        ) as conkey_names
FROM    pg_constraint as con
WHERE   con.contype = 'u';

COMMENT ON VIEW system.ukeys IS
'Scans unique key constraints.';

CREATE OR REPLACE VIEW system.ukey_names AS
SELECT  con.conrelid::regclass,
        con.conname,
        con.conkey
FROM    pg_constraint as con
WHERE   con.contype = 'u';

COMMENT ON VIEW system.ukey_names IS
'Scans unique key constraints (fast).';

CREATE OR REPLACE VIEW system.fkeys AS
SELECT  con.conrelid::regclass,
        con.conname,
        con.condeferrable,
        con.condeferred,
        con.conindid::regclass,
        con.confrelid::regclass,
        con.confupdtype,
        con.confdeltype,
        con.confmatchtype,
        con.conkey,
        ARRAY(
        SELECT  att.attname
        FROM    pg_attribute as att,
                generate_subscripts(con.conkey, 1) as k
        WHERE   att.attrelid = con.conrelid
        AND     att.attnum = con.conkey[k]
        ORDER BY k
        ) as conkey_names,
        con.confkey,
        ARRAY(
        SELECT  att.attname
        FROM    pg_attribute as att,
                generate_subscripts(con.confkey, 1) as k
        WHERE   att.attrelid = con.confrelid
        AND     att.attnum = con.confkey[k]
        ORDER BY k
        ) as confkey_names
FROM    pg_constraint as con
WHERE   con.contype = 'f';

COMMENT ON VIEW system.fkeys IS
'Scans foreign keys.';

CREATE OR REPLACE VIEW system.fkey_names AS
SELECT  con.conrelid::regclass,
        con.confrelid::regclass,
        con.conname,
        con.conkey,
        con.confkey
FROM    pg_constraint as con
WHERE   con.contype = 'f';

COMMENT ON VIEW system.fkey_names IS
'Scans fkeys (fast).';

CREATE OR REPLACE VIEW system.indexes AS
SELECT  i.relname as indname,
        i.relowner as indowner,
        i.relistemp as indistemp,
        idx.indrelid::regclass,
        am.amname as indam,
        idx.indkey,
        ARRAY(
        SELECT  pg_get_indexdef(idx.indexrelid, k + 1, true)
        FROM    generate_subscripts(idx.indkey, 1) as k
        ORDER BY k
        ) as indkey_names,
        idx.indexprs IS NOT NULL as indexprs,
        idx.indpred IS NOT NULL as indpred
FROM    pg_index as idx
JOIN    pg_class as i
ON      i.oid = idx.indexrelid
JOIN    pg_am as am
ON      i.relam = am.oid;

COMMENT ON VIEW system.indexes IS
'Scans indexes.';

CREATE OR REPLACE VIEW system.index_names AS
SELECT  idx.indrelid::regclass,
        i.relname as indname,
        idx.indkey
FROM    pg_index as idx
JOIN    pg_class as i
ON      i.oid = idx.indexrelid;

COMMENT ON VIEW system.index_names IS
'Scans indexes (fast).';