FazBrowse GitHub Viewer
|
Trending
|
URL:
|
Home
Tools:
[Download Repo ZIP]
[View Raw Code]
[Original HTTPS Page]
IvorySQL/src/tutorial/syscat.source at master · IvorySQL/IvorySQL · GitHub
IvorySQL
IvorySQL
Repository navigation
Code
Issues
435
(435)
Pull requests
276
(276)
Discussions
Actions
Projects
Wiki
Security and quality
Insights
Expand file tree
Breadcrumbs
IvorySQL
/
src
/
tutorial
/
syscat.source
Copy path
More file actions
More file actions
Latest commit
History
History
History
170 lines (151 loc) · 5.15 KB
Breadcrumbs
IvorySQL
/
src
/
tutorial
/
syscat.source
Copy path
File metadata and controls
170 lines (151 loc) · 5.15 KB
Raw
Copy raw file
Download raw file
Open symbols panel
Edit and raw actions
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
---------------------------------------------------------------------------
--
-- syscat.sql-
-- sample queries to the system catalogs
--
--
-- Portions Copyright (c) 1996-2026, 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;
--
-- 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 to default
--
RESET search_path;
Back
|
FazBrowse Home
|
New Git URL