Examples for using the system catalogs¶
The system catalogs are regular tables where PostgreSQL stores schema metadata, such as information about tables and columns, and internal bookkeeping information. You can drop and recreate the tables, add columns, insert and update values, and severely mess up your system that way. Normally, one should not change the system catalogs by hand: there are SQL commands to make all supported changes. For example, CREATE DATABASE inserts a row into the pg_database catalog — and actually creates the database on disk.
It this section we want to show examples for how to parse some of the system catalogs, making queries with the classic PyGreSQL interface.
We assume that you have already created a connection to the PostgreSQL database, as explained in the Basic examples:
>>> from pg import DB
>>> db = DB()
>>> query = db.query
Lists indices¶
This query lists all simple indices in the database:
print(query("""SELECT bc.relname AS class_name,
ic.relname AS index_name, a.attname
FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
WHERE i.indrelid = bc.oid AND i.indexrelid = ic.oid
AND i.indkey[0] = a.attnum AND a.attrelid = bc.oid
AND NOT a.attisdropped AND a.attnum>0
ORDER BY class_name, index_name, attname"""))
List user defined attributes¶
This query lists all user-defined attributes and their types in user-defined tables:
print(query("""SELECT c.relname, a.attname,
format_type(a.atttypid, a.atttypmod)
FROM pg_class c, pg_attribute a
WHERE c.relkind = 'r' AND c.relnamespace!=ALL(ARRAY[
'pg_catalog','pg_toast', 'information_schema']::regnamespace[])
AND a.attnum > 0
AND a.attrelid = c.oid
AND NOT a.attisdropped
ORDER BY relname, attname"""))
List user defined base types¶
This query lists all user defined base types:
print(query("""SELECT r.rolname, t.typname
FROM pg_type t, pg_authid r
WHERE r.oid = t.typowner
AND t.typrelid = '0'::oid and t.typelem = '0'::oid
AND r.rolname != 'postgres'
ORDER BY rolname, typname"""))
List operators¶
This query lists all right-unary operators:
print(query("""SELECT o.oprname AS right_unary,
lt.typname AS operand, result.typname AS return_type
FROM pg_operator o, pg_type lt, pg_type result
WHERE o.oprkind='r' and o.oprleft = lt.oid
AND o.oprresult = result.oid
ORDER BY operand"""))
This query lists all left-unary operators:
print(query("""SELECT o.oprname AS left_unary,
rt.typname AS operand, result.typname AS return_type
FROM pg_operator o, pg_type rt, pg_type result
WHERE o.oprkind='l' AND o.oprright = rt.oid
AND o.oprresult = result.oid
ORDER BY operand"""))
And this one lists all of the binary operators:
print(query("""SELECT o.oprname AS binary_op,
rt.typname AS right_opr, lt.typname AS left_opr,
result.typname AS return_type
FROM pg_operator o, pg_type rt, pg_type lt, pg_type result
WHERE o.oprkind = 'b' AND o.oprright = rt.oid
AND o.oprleft = lt.oid AND o.oprresult = result.oid"""))
List functions of a language¶
Given a programming language, this query returns the name, args and return type from all functions of a language:
language = 'sql'
print(query("""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 = $1
ORDER BY proname""", (language,)))
List aggregate functions¶
This query lists all of the aggregate functions and the type to which they can be applied:
print(query("""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"""))
List operator families¶
The following query lists all defined operator families and all the operators included in each family:
print(query("""SELECT am.amname, opf.opfname, amop.amopopr::regoperator
FROM pg_am am, pg_opfamily opf, pg_amop amop
WHERE opf.opfmethod = am.oid
AND amop.amopfamily = opf.oid
ORDER BY amname, opfname, amopopr"""))