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
|
---------------------------------------------------------------------------
--
-- syscat.sql-
-- sample queries to the system catalogs
--
--
-- Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
-- Portions Copyright (c) 1994, Regents of the University of California
--
-- $Id: syscat.source,v 1.8 2003/05/28 16:04:02 tgl Exp $
--
---------------------------------------------------------------------------
--
-- lists the name of all database adminstrators and the name of their
-- database(s)
--
SELECT usename, datname
FROM pg_user, pg_database
WHERE usesysid = datdba
ORDER BY usename, datname;
--
-- lists all user-defined classes
--
SELECT relname
FROM pg_class
WHERE relkind = 'r' -- not indices, views, etc
and relname !~ '^pg_' -- not catalogs
ORDER BY relname;
--
-- lists all simple indices (ie. those that are defined over one simple
-- column reference)
--
SELECT bc.relname AS class_name,
ic.relname AS index_name,
a.attname
FROM pg_class bc, -- base class
pg_class ic, -- index class
pg_index i,
pg_attribute a -- att in base
WHERE 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 class_name, index_name, attname;
--
-- lists the user-defined attributes and their types for all user-defined
-- classes
--
SELECT c.relname, a.attname, t.typname
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relkind = 'r' -- no indices
and c.relname !~ '^pg_' -- no catalogs
and a.attnum > 0 -- no system att's
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY relname, attname;
--
-- lists all user-defined base types (not including array types)
--
SELECT u.usename, t.typname
FROM pg_type t, pg_user u
WHERE u.usesysid = t.typowner
and t.typrelid = '0'::oid -- no complex types
and t.typelem = '0'::oid -- no arrays
and u.usename <> 'postgres'
ORDER BY usename, typname;
--
-- lists all left unary operators
--
SELECT o.oprname AS left_unary,
right_type.typname AS operand,
result.typname AS return_type
FROM pg_operator o, pg_type right_type, pg_type result
WHERE o.oprkind = 'l' -- left unary
and o.oprright = right_type.oid
and o.oprresult = result.oid
ORDER BY operand;
--
-- lists all right unary operators
--
SELECT o.oprname AS right_unary,
left_type.typname AS operand,
result.typname AS return_type
FROM pg_operator o, pg_type left_type, pg_type result
WHERE o.oprkind = 'r' -- right unary
and o.oprleft = left_type.oid
and o.oprresult = result.oid
ORDER BY operand;
--
-- lists all binary operators
--
SELECT o.oprname AS binary_op,
left_type.typname AS left_opr,
right_type.typname AS right_opr,
result.typname AS return_type
FROM pg_operator o, pg_type left_type, pg_type right_type, pg_type result
WHERE o.oprkind = 'b' -- binary
and o.oprleft = left_type.oid
and o.oprright = right_type.oid
and o.oprresult = result.oid
ORDER BY left_opr, right_opr;
--
-- lists the name, number of arguments and the return type of all user-defined
-- C functions
--
SELECT p.proname, p.pronargs, t.typname
FROM pg_proc p, pg_language l, pg_type t
WHERE p.prolang = l.oid
and p.prorettype = t.oid
and l.lanname = 'c'
ORDER BY proname;
--
-- lists all aggregate functions and the types to which they can be applied
--
SELECT p.proname, t.typname
FROM pg_aggregate a, pg_proc p, pg_type t
WHERE a.aggfnoid = p.oid
and p.proargtypes[0] = t.oid
ORDER BY 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 am.amname, opc.opcname, opr.oprname
FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr
WHERE opc.opcamid = am.oid
and amop.amopclaid = opc.oid
and amop.amopopr = opr.oid
ORDER BY amname, opcname, oprname;
|
