1
---------------------------------------------------------------------------
4
-- sample queries to the system catalogs
7
-- Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
8
-- Portions Copyright (c) 1994, Regents of the University of California
10
-- $PostgreSQL: pgsql/src/tutorial/syscat.source,v 1.14 2004-12-31 22:04:05 pgsql Exp $
12
---------------------------------------------------------------------------
15
-- Sets the schema search path to pg_catalog first, so that we do not
16
-- need to qualify every system object
18
SET SEARCH_PATH TO pg_catalog;
21
-- lists the name of all database adminstrators and the name of their
24
SELECT usename, datname
25
FROM pg_user, pg_database
26
WHERE usesysid = datdba
27
ORDER BY usename, datname;
30
-- lists all user-defined classes
32
SELECT n.nspname, c.relname
33
FROM pg_class c, pg_namespace n
34
WHERE c.relnamespace=n.oid
35
and c.relkind = 'r' -- not indices, views, etc
36
and n.nspname not like 'pg\\_%' -- not catalogs
37
and n.nspname != 'information_schema' -- not information_schema
38
ORDER BY nspname, relname;
42
-- lists all simple indices (ie. those that are defined over one simple
45
SELECT n.nspname AS schema_name,
46
bc.relname AS class_name,
47
ic.relname AS index_name,
50
pg_class bc, -- base class
51
pg_class ic, -- index class
53
pg_attribute a -- att in base
54
WHERE bc.relnamespace = n.oid
55
and i.indrelid = bc.oid
56
and i.indexrelid = ic.oid
57
and i.indkey[0] = a.attnum
59
and a.attrelid = bc.oid
60
ORDER BY schema_name, class_name, index_name, attname;
64
-- lists the user-defined attributes and their types for all user-defined
67
SELECT n.nspname, c.relname, a.attname, format_type(t.oid, null) as typname
68
FROM pg_namespace n, pg_class c,
69
pg_attribute a, pg_type t
70
WHERE n.oid = c.relnamespace
71
and c.relkind = 'r' -- no indices
72
and n.nspname not like 'pg\\_%' -- no catalogs
73
and n.nspname != 'information_schema' -- no information_schema
74
and a.attnum > 0 -- no system att's
75
and not a.attisdropped -- no dropped columns
76
and a.attrelid = c.oid
77
and a.atttypid = t.oid
78
ORDER BY nspname, relname, attname;
82
-- lists all user-defined base types (not including array types)
84
SELECT n.nspname, u.usename, format_type(t.oid, null) as typname
85
FROM pg_type t, pg_user u, pg_namespace n
86
WHERE u.usesysid = t.typowner
87
and t.typnamespace = n.oid
88
and t.typrelid = '0'::oid -- no complex types
89
and t.typelem = '0'::oid -- no arrays
90
and n.nspname not like 'pg\\_%' -- no catalogs
91
and n.nspname != 'information_schema' -- no information_schema
92
ORDER BY nspname, usename, typname;
96
-- lists all left unary operators
98
SELECT n.nspname, o.oprname AS left_unary,
99
format_type(right_type.oid, null) AS operand,
100
format_type(result.oid, null) AS return_type
101
FROM pg_namespace n, pg_operator o,
102
pg_type right_type, pg_type result
103
WHERE o.oprnamespace = n.oid
104
and o.oprkind = 'l' -- left unary
105
and o.oprright = right_type.oid
106
and o.oprresult = result.oid
107
ORDER BY nspname, operand;
111
-- lists all right unary operators
113
SELECT n.nspname, o.oprname AS right_unary,
114
format_type(left_type.oid, null) AS operand,
115
format_type(result.oid, null) AS return_type
116
FROM pg_namespace n, pg_operator o,
117
pg_type left_type, pg_type result
118
WHERE o.oprnamespace = n.oid
119
and o.oprkind = 'r' -- right unary
120
and o.oprleft = left_type.oid
121
and o.oprresult = result.oid
122
ORDER BY nspname, operand;
125
-- lists all binary operators
127
SELECT n.nspname, o.oprname AS binary_op,
128
format_type(left_type.oid, null) AS left_opr,
129
format_type(right_type.oid, null) AS right_opr,
130
format_type(result.oid, null) AS return_type
131
FROM pg_namespace n, pg_operator o, pg_type left_type,
132
pg_type right_type, pg_type result
133
WHERE o.oprnamespace = n.oid
134
and o.oprkind = 'b' -- binary
135
and o.oprleft = left_type.oid
136
and o.oprright = right_type.oid
137
and o.oprresult = result.oid
138
ORDER BY nspname, left_opr, right_opr;
142
-- lists the name, number of arguments and the return type of all user-defined
145
SELECT n.nspname, p.proname, p.pronargs, format_type(t.oid, null) as return_type
146
FROM pg_namespace n, pg_proc p,
147
pg_language l, pg_type t
148
WHERE p.pronamespace = n.oid
149
and n.nspname not like 'pg\\_%' -- no catalogs
150
and n.nspname != 'information_schema' -- no information_schema
151
and p.prolang = l.oid
152
and p.prorettype = t.oid
154
ORDER BY nspname, proname, pronargs, return_type;
157
-- lists all aggregate functions and the types to which they can be applied
159
SELECT n.nspname, p.proname, format_type(t.oid, null) as typname
160
FROM pg_namespace n, pg_aggregate a,
162
WHERE p.pronamespace = n.oid
163
and a.aggfnoid = p.oid
164
and p.proargtypes[0] = t.oid
165
ORDER BY nspname, proname, typname;
169
-- lists all the operator classes that can be used with each access method
170
-- as well as the operators that cn be used with the respective operator
173
SELECT n.nspname, am.amname, opc.opcname, opr.oprname
174
FROM pg_namespace n, pg_am am, pg_opclass opc,
175
pg_amop amop, pg_operator opr
176
WHERE opc.opcnamespace = n.oid
177
and opc.opcamid = am.oid
178
and amop.amopclaid = opc.oid
179
and amop.amopopr = opr.oid
180
ORDER BY nspname, amname, opcname, oprname;
183
-- Reset the search path