On 9/5/07, Josh Trutwin <josh@trutwins.homeip.net> wrote:
> On Wed, 5 Sep 2007 19:08:33 -0400
> "Merlin Moncure" <mmoncure@gmail.com> wrote:
>
> > 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;
>
> Beautiful, thanks - I had to change one thing - c2.relname is the
> constraint name - c.relname is the table name.
right, my mistake...
> Couple questions:
>
> 1.) The ORDER BY - I assume this can be reduced to "ORDER BY
> c.relname"?
actually, 'order by' in views, unless in subquery, is bad form and
should be removed. I lifted a lot of this query by the way from
psql...using the psql -E invocation to get the sql it makes when doing
\d :-).
> 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
Actually, I generally use joins (although I type just JOIN, not INNER
JOIN) and prefer JOIN USING to JOIN ON where it can be used. For
simple queries with no left or right joins or certain special cases I
might use the where clause (it rarely makes a difference in the
planner AFAIK).
merlin