Re: OID Usage

On Fri, Jan 14, 2005 at 06:39:25PM -0600, Jim C. Nasby wrote:
>
> If you do manage to write a function that will do this I hope you can
> share it with the community. IMHO PostgreSQL could do with more
> functions for querying the system catalogs.

Here's a first attempt at a view that shows tables and their primary
key columns and sequences. I chose a view instead of a function
because a view shows everything in the database with a single query,
which simplifies visual examination of the results. Modify it or
convert it to a function as needed.

The view assumes single-column primary keys defined as SERIAL types.
Properly handling other situations would be a desirable enhancement.

I've done only trivial testing, so if anybody finds a situation
where the view fails (taking the above assumption into account)
then please describe it.

CREATE OR REPLACE VIEW pk_sequence AS
SELECT n.nspname AS tableschema,
c.relname AS tablename,
a.attname AS pkcol,
n2.nspname AS seqschema,
c2.relname AS seqname
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
JOIN pg_index AS i ON i.indrelid = c.oid AND i.indisprimary IS TRUE
JOIN pg_attribute AS a ON a.attrelid = c.oid AND a.attnum = i.indkey[0]
JOIN pg_depend AS d ON d.refobjid = c.oid AND d.refobjsubid = i.indkey[0]
JOIN pg_class AS c2 ON c2.oid = d.objid AND c2.relkind = 'S'
JOIN pg_namespace AS n2 ON n2.oid = c2.relnamespace;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/