---------------------------------------------------------------------------
--
-- syscat.sql-
--    sample queries to the system catalogs
--
--
-- Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
-- Portions Copyright (c) 1994, Regents of the University of California
--
-- $PostgreSQL: pgsql/src/tutorial/syscat.source,v 1.16 2006/03/05 15:59:11 momjian Exp $
--
---------------------------------------------------------------------------

--
-- Sets the schema search path to pg_catalog first, so that we do not
-- need to qualify every system object
--
SET SEARCH_PATH TO pg_catalog;

--
-- lists the names of all database owners and the name of their database(s)
--
SELECT rolname, datname
  FROM pg_roles, pg_database
  WHERE pg_roles.oid = datdba
  ORDER BY rolname, datname;

--
-- lists all user-defined classes
--
SELECT n.nspname, c.relname
  FROM pg_class c, pg_namespace n
  WHERE c.relnamespace=n.oid
    and c.relkind = 'r'                   -- not indices, views, etc
    and n.nspname not like 'pg\\_%'       -- not catalogs
    and n.nspname != 'information_schema' -- not information_schema
  ORDER BY nspname, relname;


--
-- lists all simple indices (ie. those that are defined over one simple
-- column reference)
--
SELECT n.nspname AS schema_name,
       bc.relname AS class_name, 
       ic.relname AS index_name, 
       a.attname
  FROM pg_namespace n,
       pg_class bc,             -- base class
       pg_class ic,             -- index class
       pg_index i,
       pg_attribute a           -- att in base
  WHERE bc.relnamespace = n.oid
     and i.indrelid = bc.oid
     and i.indexrelid = ic.oid
     and i.indkey[0] = a.attnum
     and i.indnatts = 1
     and a.attrelid = bc.oid
  ORDER BY schema_name, class_name, index_name, attname;


--
-- lists the user-defined attributes and their types for all user-defined
-- classes
--
SELECT n.nspname, c.relname, a.attname, format_type(t.oid, null) as typname
  FROM pg_namespace n, pg_class c, 
       pg_attribute a, pg_type t
  WHERE n.oid = c.relnamespace
    and c.relkind = 'r'     -- no indices
    and n.nspname not like 'pg\\_%' -- no catalogs
    and n.nspname != 'information_schema' -- no information_schema
    and a.attnum > 0        -- no system att's
    and not a.attisdropped   -- no dropped columns
    and a.attrelid = c.oid
    and a.atttypid = t.oid
  ORDER BY nspname, relname, attname;


--
-- lists all user-defined base types (not including array types)
--
SELECT n.nspname, r.rolname, format_type(t.oid, null) as typname
  FROM pg_type t, pg_roles r, pg_namespace n
  WHERE r.oid = t.typowner
    and t.typnamespace = n.oid
    and t.typrelid = 0   -- no complex types
    and t.typelem = 0    -- no arrays
    and n.nspname not like 'pg\\_%' -- no built-in types
    and n.nspname != 'information_schema' -- no information_schema
  ORDER BY nspname, rolname, typname;


--
-- lists all left unary operators
--
SELECT n.nspname, o.oprname AS left_unary, 
       format_type(right_type.oid, null) AS operand,
       format_type(result.oid, null) AS return_type
  FROM pg_namespace n, pg_operator o, 
       pg_type right_type, pg_type result
  WHERE o.oprnamespace = n.oid
    and o.oprkind = 'l'           -- left unary
    and o.oprright = right_type.oid
    and o.oprresult = result.oid
  ORDER BY nspname, operand;


--
-- lists all right unary operators
--
SELECT n.nspname, o.oprname AS right_unary, 
       format_type(left_type.oid, null) AS operand,
       format_type(result.oid, null) AS return_type
  FROM pg_namespace n, pg_operator o, 
       pg_type left_type, pg_type result
  WHERE o.oprnamespace = n.oid
    and o.oprkind = 'r'          -- right unary
    and o.oprleft = left_type.oid
    and o.oprresult = result.oid
  ORDER BY nspname, operand;

--
-- lists all binary operators
--
SELECT n.nspname, o.oprname AS binary_op,
       format_type(left_type.oid, null) AS left_opr,
       format_type(right_type.oid, null) AS right_opr,
       format_type(result.oid, null) AS return_type
  FROM pg_namespace n, pg_operator o, pg_type left_type, 
       pg_type right_type, pg_type result
  WHERE o.oprnamespace = n.oid
    and o.oprkind = 'b'         -- binary
    and o.oprleft = left_type.oid
    and o.oprright = right_type.oid
    and o.oprresult = result.oid
  ORDER BY nspname, left_opr, right_opr;


--
-- lists the name, number of arguments and the return type of all user-defined
-- C functions
--
SELECT n.nspname, p.proname, p.pronargs, format_type(t.oid, null) as return_type
  FROM pg_namespace n, pg_proc p, 
       pg_language l, pg_type t
  WHERE p.pronamespace = n.oid
    and n.nspname not like 'pg\\_%' -- no catalogs
    and n.nspname != 'information_schema' -- no information_schema
    and p.prolang = l.oid 
    and p.prorettype = t.oid
    and l.lanname = 'c'
  ORDER BY nspname, proname, pronargs, return_type;

--
-- lists all aggregate functions and the types to which they can be applied
--
SELECT n.nspname, p.proname, format_type(t.oid, null) as typname
  FROM pg_namespace n, pg_aggregate a, 
       pg_proc p, pg_type t
  WHERE p.pronamespace = n.oid
    and a.aggfnoid = p.oid
    and p.proargtypes[0] = t.oid
  ORDER BY nspname, proname, typname;


--
-- lists all the operator classes that can be used with each access method
-- as well as the operators that cn be used with the respective operator
-- classes
--
SELECT n.nspname, am.amname, opc.opcname, opr.oprname
  FROM pg_namespace n, pg_am am, pg_opclass opc, 
       pg_amop amop, pg_operator opr
  WHERE opc.opcnamespace = n.oid
    and opc.opcamid = am.oid
    and amop.amopclaid = opc.oid
    and amop.amopopr = opr.oid
  ORDER BY nspname, amname, opcname, oprname;

--
-- Reset the search path
--
RESET SEARCH_PATH;