Re: Querying database for table pk - better way?

On Wed, 5 Sep 2007 19:08:33 -0400
"Merlin Moncure" <mmoncure(at)gmail(dot)com> wrote:

> On 9/5/07, Josh Trutwin <josh(at)trutwins(dot)homeip(dot)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;

Beautiful, thanks - I had to change one thing - c2.relname is the
constraint name - c.relname is the table name.

Couple questions:

1.) The ORDER BY - I assume this can be reduced to "ORDER BY
c.relname"?

2.) Can you explain that substring line? What in the world is "from
E'\\((.*)\\)')" doing? Somehow it gets the column name....

3.) I changed the WHERE clause to use INNER JOIN's - is it just your
personal preference not to use INNER JOINs or does it actually have
an impact on the planner? I prefer to separate them so I can
visually keep the join conditions separate from the extra stuff in the
WHERE clause that filters the results.

My version (don't need the OID col):

CREATE OR REPLACE VIEW PKEYS (schema_name, table_name, column_name) AS
SELECT nspname, c.relname,
SUBSTRING(pg_catalog.pg_get_indexdef(i.indexrelid, 0, TRUE)
FROM E'\\((.*)\\)')
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_index i ON c.oid = i.indrelid
INNER JOIN pg_namespace n ON c.relnamespace = n.oid
INNER JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid
WHERE c.relkind = 'r' AND i.indisprimary
ORDER BY c.relname;

Thanks again,

Josh