source: branches/4.x/docs/contents/postgres/syscat.rst @ 746

Last change on this file since 746 was 746, checked in by cito, 4 years ago

Make references in the Postgres tutorial functional

File size: 4.5 KB
Line 
1Examples for using the system catalogs
2======================================
3
4.. py:currentmodule:: pg
5
6The system catalogs are regular tables where PostgreSQL stores schema metadata,
7such as information about tables and columns, and internal bookkeeping
8information. You can drop and recreate the tables, add columns, insert and
9update values, and severely mess up your system that way. Normally, one
10should not change the system catalogs by hand, there are always SQL commands
11to do that. For example, CREATE DATABASE inserts a row into the *pg_database*
12catalog — and actually creates the database on disk.
13
14It this section we want to show examples for how to parse some of the system
15catalogs, making queries with the classic PyGreSQL interface.
16
17We assume that you have already created a connection to the PostgreSQL
18database, as explained in the :doc:`basic`::
19
20    >>> from pg import DB
21    >>> db = DB()
22    >>> query = query
23
24Lists indices
25-------------
26
27This query lists all simple indices in the database::
28
29    print query("""SELECT bc.relname AS class_name,
30            ic.relname AS index_name, a.attname
31        FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
32        WHERE i.indrelid = bc.oid AND i.indexrelid = ic.oid
33            AND i.indkey[0] = a.attnum AND a.attrelid = bc.oid
34            AND NOT a.attisdropped
35        ORDER BY class_name, index_name, attname""")
36
37
38List user defined attributes
39----------------------------
40
41This query lists all user defined attributes and their type
42in user-defined classes::
43
44    print query("""SELECT c.relname, a.attname, t.typname
45        FROM pg_class c, pg_attribute a, pg_type t
46        WHERE c.relkind = 'r' and c.relname !~ '^pg_'
47            AND c.relname !~ '^Inv' and a.attnum > 0
48            AND a.attrelid = c.oid and a.atttypid = t.oid
49            AND NOT a.attisdropped
50        ORDER BY relname, attname""")
51
52List user defined base types
53----------------------------
54
55This query lists all user defined base types::
56
57    print query("""SELECT r.rolname, t.typname
58        FROM pg_type t, pg_authid r
59        WHERE r.oid = t.typowner
60            AND t.typrelid = '0'::oid and t.typelem = '0'::oid
61            AND r.rolname != 'postgres'
62        ORDER BY rolname, typname""")
63
64
65List  operators
66---------------
67
68This query lists all right-unary operators::
69
70    print query("""SELECT o.oprname AS right_unary,
71            lt.typname AS operand, result.typname AS return_type
72        FROM pg_operator o, pg_type lt, pg_type result
73        WHERE o.oprkind='r' and o.oprleft = lt.oid
74            AND o.oprresult = result.oid
75        ORDER BY operand""")
76
77
78This query lists all left-unary operators::
79
80    print query("""SELECT o.oprname AS left_unary,
81            rt.typname AS operand, result.typname AS return_type
82        FROM pg_operator o, pg_type rt, pg_type result
83        WHERE o.oprkind='l' AND o.oprright = rt.oid
84            AND o.oprresult = result.oid
85        ORDER BY operand""")
86
87
88And this one lists all of the binary operators::
89
90    print query("""SELECT o.oprname AS binary_op,
91            rt.typname AS right_opr, lt.typname AS left_opr,
92            result.typname AS return_type
93        FROM pg_operator o, pg_type rt, pg_type lt, pg_type result
94        WHERE o.oprkind = 'b' AND o.oprright = rt.oid
95            AND o.oprleft = lt.oid AND o.oprresult = result.oid""")
96
97
98List functions of a language
99----------------------------
100
101Given a programming language, this query returns the name, args and return
102type from all functions of a language::
103
104    language = 'sql'
105    print query("""SELECT p.proname, p.pronargs, t.typname
106        FROM pg_proc p, pg_language l, pg_type t
107        WHERE p.prolang = l.oid AND p.prorettype = t.oid
108            AND l.lanname = $1
109        ORDER BY proname""", (language,))
110
111
112List aggregate functions
113------------------------
114
115This query lists all of the aggregate functions and the type to which
116they can be applied::
117
118    print query("""SELECT p.proname, t.typname
119        FROM pg_aggregate a, pg_proc p, pg_type t
120        WHERE a.aggfnoid = p.oid
121            and p.proargtypes[0] = t.oid
122        ORDER BY proname, typname""")
123
124List operator families
125----------------------
126
127The following query lists all defined operator families and all the operators
128included in each family::
129
130    print query("""SELECT am.amname, opf.opfname, amop.amopopr::regoperator
131        FROM pg_am am, pg_opfamily opf, pg_amop amop
132        WHERE opf.opfmethod = am.oid
133            AND amop.amopfamily = opf.oid
134        ORDER BY amname, opfname, amopopr""")
Note: See TracBrowser for help on using the repository browser.