Обсуждение: primary key query

Hi,

I've got a table with the following schema:

create table enumerations.eligibility_type (
        id serial primary key
) inherits (enumerations.enumeration);

However if I do:
SELECT pg_index.indisprimary,
            pg_catalog.pg_get_indexdef(pg_index.indexrelid)
        FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
            pg_catalog.pg_index AS pg_index
        WHERE c.relname = 'enumerations.eligibility_type'
       AND c.oid = pg_index.indrelid
      AND pg_index.indexrelid = c2.oid AND pg_index.indisprimary;

I get nothing but if I do:
SELECT pg_index.indisprimary,
pg_catalog.pg_get_indexdef(pg_index.indexrelid)
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index AS pg_index
WHERE c.relname = 'eligibility_type' AND c.oid = pg_index.indrelid AND
pg_index.indexrelid = c2.oid AND pg_index.indisprimary;

I get:
 indisprimary |                                       pg_get_indexdef
--------------+---------------------------------------------------------------------------------------------
 t            | CREATE UNIQUE INDEX eligibility_type_pkey ON
enumerations.eligibility_type USING btree (id)

Why is relname 'eligibility_type' and not 'enumerations.eligibility_type'?

Cheers,
Shuying

On Thu, Nov 03, 2005 at 11:46:58AM +1100, Shuying Wang wrote:
> Why is relname 'eligibility_type' and not 'enumerations.eligibility_type'?

pg_class.relname stores only the relation name; if you want the
schema name then join pg_class.relnamespace and pg_namespace.oid
and refer to pg_namespace.nspname.  Another way would be to cast
'schemaname.relationname' to regclass and compare it to pg_class.oid:

SELECT ...
FROM pg_class c ...
WHERE c.oid = 'enumerations.eligibility_type'::regclass
  AND ...

--
Michael Fuhr