Accessing schema data in information schema

Peter Eisentraut <peter_e@gmx.net>

From: Peter Eisentraut <peter_e@gmx.net>
To: pgsql-hackers@postgresql.org
Date: 2006-03-22T20:41:46Z
Lists: pgsql-hackers
I'm updating the information schema for SQL:2003.  I'm having some 
difficulties with the "sequences" view.  It should look approximately 
like this (uninteresting stuff omitted):

CREATE VIEW sequences AS
  SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
         CAST(nc.nspname AS sql_identifier) AS sequence_schema,
         CAST(c.relname AS sql_identifier) AS sequence_name,
         CAST(null AS cardinal_number) AS maximum_value, -- FIXME
         CAST(null AS cardinal_number) AS minimum_value, -- FIXME
         CAST(null AS cardinal_number) AS increment,     -- FIXME
         CAST(null AS character_data) AS cycle_option    -- FIXME
  FROM pg_namespace nc, pg_class c
  WHERE c.relnamespace = nc.oid
        AND c.relkind = 's';

How does one get at the missing fields.  The only way I know is 
selecting from the sequence, but how does one work this into this 
query?  Somehow it seems that these things should be stored in a real 
system catalog.

Ideas (short of using PERFORM in PL/pgSQL)?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/