-- sample queries to the system catalogs

--

--

-- Copyright (c) 1994, Regents of the University of California

-- Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group

-- Portions Copyright (c) 1994, Regents of the University of California

--

-- $Id: syscat.source,v 1.3 1998/03/20 04:12:25 momjian Exp $

-- $Id: syscat.source,v 1.4 2001/06/13 19:31:56 tgl Exp $

--

---------------------------------------------------------------------------

--

SELECT usename, datname

FROM pg_user, pg_database

WHERE usesysid =int2in(int4out(datdba))

WHERE usesysid = datdba

ORDER BY usename, datname;

--

-- lists all user-defined classes

--

SELECT relname

FROM pg_class

WHERE relkind = 'r' -- not indices

WHERE relkind = 'r' -- not indices, views, etc

and relname !~ '^pg_' -- not catalogs

and relname !~ '^Inv' -- not large objects

ORDER BY relname;

--

-- lists all simpleindicies (ie. those that are not defined over a function

-- lists all simpleindices (ie. those that are not defined over a function

-- of several attributes)

--

SELECT bc.relname AS class_name,

FROM pg_class c, pg_attribute a, pg_type t

WHERE c.relkind = 'r' -- no indices

and c.relname !~ '^pg_' -- no catalogs

and c.relname !~ '^Inv' -- no large objects

and a.attnum > 0 -- no system att's

and a.attrelid = c.oid

and a.atttypid = t.oid

--

SELECT u.usename, t.typname

FROM pg_type t, pg_user u

WHERE u.usesysid =int2in(int4out(t.typowner))

WHERE u.usesysid = t.typowner

and t.typrelid = '0'::oid -- no complex types

and t.typelem = '0'::oid -- no arrays

and u.usename <> 'postgres'

and amop.amopclaid = opc.oid

and amop.amopopr = opr.oid

ORDER BY amname, opcname, oprname;