Re: Querying database for table pk - better way? - Mailing list pgsql-general

On 9/5/07, Josh Trutwin <josh@trutwins.homeip.net> wrote:
> I have a php application that needs to query the PK of a table - I'm
> currently using this from the information_schema views:

try this:
CREATE OR REPLACE VIEW PKEYS AS
    SELECT nspname as schema, c2.oid as tableoid, c2.relname as table,
      substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) from
E'\\((.*)\\)')
    FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index i, pg_namespace n
    WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND c.relkind = 'r'
      AND i.indisprimary AND c.relnamespace = n.oid
    ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;

merlin