src/tutorial/syscat.source

   1 ---------------------------------------------------------------------------
   2 --
   3 -- syscat.sql-
   4 --    sample queries to the system catalogs
   5 --
   6 --
   7 -- Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
   8 -- Portions Copyright (c) 1994, Regents of the University of California
   9 --
  10 -- src/tutorial/syscat.source
  11 --
  12 ---------------------------------------------------------------------------
  13
  14 --
  15 -- Sets the schema search path to pg_catalog first, so that we do not
  16 -- need to qualify every system object
  17 --
  18 SET search_path TO pg_catalog;
  19
  20 -- The LIKE pattern language requires underscores to be escaped, so make
  21 -- sure the backslashes are not misinterpreted.
  22 SET standard_conforming_strings TO on;
  23
  24 --
  25 -- lists the names of all database owners and the name of their database(s)
  26 --
  27 SELECT rolname, datname
  28   FROM pg_roles, pg_database
  29   WHERE pg_roles.oid = datdba
  30   ORDER BY rolname, datname;
  31
  32 --
  33 -- lists all user-defined classes
  34 --
  35 SELECT n.nspname, c.relname
  36   FROM pg_class c, pg_namespace n
  37   WHERE c.relnamespace=n.oid
  38     and c.relkind = 'r'                   -- not indices, views, etc
  39     and n.nspname not like 'pg\_%'       -- not catalogs
  40     and n.nspname != 'information_schema' -- not information_schema
  41   ORDER BY nspname, relname;
  42
  43
  44 --
  45 -- lists all simple indices (ie. those that are defined over one simple
  46 -- column reference)
  47 --
  48 SELECT n.nspname AS schema_name,
  49        bc.relname AS class_name,
  50        ic.relname AS index_name,
  51        a.attname
  52   FROM pg_namespace n,
  53        pg_class bc,             -- base class
  54        pg_class ic,             -- index class
  55        pg_index i,
  56        pg_attribute a           -- att in base
  57   WHERE bc.relnamespace = n.oid
  58      and i.indrelid = bc.oid
  59      and i.indexrelid = ic.oid
  60      and i.indkey[0] = a.attnum
  61      and i.indnatts = 1
  62      and a.attrelid = bc.oid
  63   ORDER BY schema_name, class_name, index_name, attname;
  64
  65
  66 --
  67 -- lists the user-defined attributes and their types for all user-defined
  68 -- classes
  69 --
  70 SELECT n.nspname, c.relname, a.attname, format_type(t.oid, null) as typname
  71   FROM pg_namespace n, pg_class c,
  72        pg_attribute a, pg_type t
  73   WHERE n.oid = c.relnamespace
  74     and c.relkind = 'r'     -- no indices
  75     and n.nspname not like 'pg\_%' -- no catalogs
  76     and n.nspname != 'information_schema' -- no information_schema
  77     and a.attnum > 0        -- no system att's
  78     and not a.attisdropped   -- no dropped columns
  79     and a.attrelid = c.oid
  80     and a.atttypid = t.oid
  81   ORDER BY nspname, relname, attname;
  82
  83
  84 --
  85 -- lists all user-defined base types (not including array types)
  86 --
  87 SELECT n.nspname, r.rolname, format_type(t.oid, null) as typname
  88   FROM pg_type t, pg_roles r, pg_namespace n
  89   WHERE r.oid = t.typowner
  90     and t.typnamespace = n.oid
  91     and t.typrelid = 0   -- no complex types
  92     and t.typelem = 0    -- no arrays
  93     and n.nspname not like 'pg\_%' -- no built-in types
  94     and n.nspname != 'information_schema' -- no information_schema
  95   ORDER BY nspname, rolname, typname;
  96
  97
  98 --
  99 -- lists all prefix operators
 100 --
 101 SELECT n.nspname, o.oprname AS prefix_op,
 102        format_type(right_type.oid, null) AS operand,
 103        format_type(result.oid, null) AS return_type
 104   FROM pg_namespace n, pg_operator o,
 105        pg_type right_type, pg_type result
 106   WHERE o.oprnamespace = n.oid
 107     and o.oprkind = 'l'           -- prefix ("left unary")
 108     and o.oprright = right_type.oid
 109     and o.oprresult = result.oid
 110   ORDER BY nspname, operand;
 111
 112
 113 --
 114 -- lists all infix operators
 115 --
 116 SELECT n.nspname, o.oprname AS binary_op,
 117        format_type(left_type.oid, null) AS left_opr,
 118        format_type(right_type.oid, null) AS right_opr,
 119        format_type(result.oid, null) AS return_type
 120   FROM pg_namespace n, pg_operator o, pg_type left_type,
 121        pg_type right_type, pg_type result
 122   WHERE o.oprnamespace = n.oid
 123     and o.oprkind = 'b'         -- infix ("binary")
 124     and o.oprleft = left_type.oid
 125     and o.oprright = right_type.oid
 126     and o.oprresult = result.oid
 127   ORDER BY nspname, left_opr, right_opr;
 128
 129
 130 --
 131 -- lists the name, number of arguments and the return type of all user-defined
 132 -- C functions
 133 --
 134 SELECT n.nspname, p.proname, p.pronargs, format_type(t.oid, null) as return_type
 135   FROM pg_namespace n, pg_proc p,
 136        pg_language l, pg_type t
 137   WHERE p.pronamespace = n.oid
 138     and n.nspname not like 'pg\_%' -- no catalogs
 139     and n.nspname != 'information_schema' -- no information_schema
 140     and p.prolang = l.oid
 141     and p.prorettype = t.oid
 142     and l.lanname = 'c'
 143   ORDER BY nspname, proname, pronargs, return_type;
 144
 145 --
 146 -- lists all aggregate functions and the types to which they can be applied
 147 --
 148 SELECT n.nspname, p.proname, format_type(t.oid, null) as typname
 149   FROM pg_namespace n, pg_aggregate a,
 150        pg_proc p, pg_type t
 151   WHERE p.pronamespace = n.oid
 152     and a.aggfnoid = p.oid
 153     and p.proargtypes[0] = t.oid
 154   ORDER BY nspname, proname, typname;
 155
 156
 157 --
 158 -- lists all the operator families that can be used with each access method
 159 -- as well as the operators that can be used with the respective operator
 160 -- families
 161 --
 162 SELECT am.amname, n.nspname, opf.opfname, opr.oprname
 163   FROM pg_namespace n, pg_am am, pg_opfamily opf,
 164        pg_amop amop, pg_operator opr
 165   WHERE opf.opfnamespace = n.oid
 166     and opf.opfmethod = am.oid
 167     and amop.amopfamily = opf.oid
 168     and amop.amopopr = opr.oid
 169   ORDER BY nspname, amname, opfname, oprname;
 170
 171 --
 172 -- Reset the search path and standard_conforming_strings to their defaults
 173 --
 174 RESET search_path;
 175 RESET standard_conforming_strings;