summaryrefslogtreecommitdiffstats
path: root/src/tutorial/syscat.source
blob: 6458c3237203706a0741d5eb9fce059f1a099300 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
---------------------------------------------------------------------------
--
-- syscat.sql-
--    sample queries to the system catalogs
--
--
-- Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
-- Portions Copyright (c) 1994, Regents of the University of California
--
-- src/tutorial/syscat.source
--
---------------------------------------------------------------------------

--
-- 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;

-- The LIKE pattern language requires underscores to be escaped, so make
-- sure the backslashes are not misinterpreted.
SET standard_conforming_strings TO on;

--
-- 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 prefix operators
--
SELECT n.nspname, o.oprname AS prefix_op,
       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'           -- prefix ("left unary")
    and o.oprright = right_type.oid
    and o.oprresult = result.oid
  ORDER BY nspname, operand;


--
-- lists all infix 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'         -- infix ("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 families that can be used with each access method
-- as well as the operators that can be used with the respective operator
-- families
--
SELECT am.amname, n.nspname, opf.opfname, opr.oprname
  FROM pg_namespace n, pg_am am, pg_opfamily opf,
       pg_amop amop, pg_operator opr
  WHERE opf.opfnamespace = n.oid
    and opf.opfmethod = am.oid
    and amop.amopfamily = opf.oid
    and amop.amopopr = opr.oid
  ORDER BY nspname, amname, opfname, oprname;

--
-- Reset the search path and standard_conforming_strings to their defaults
--
RESET search_path;
RESET standard_conforming_strings;