Re: Returning the name of a primary key

Tom, Juan,

Wouldn't this simple SQL do the trick?

CREATE OR REPLACE FUNCTION pk_column(text) RETURNS SETOF text   AS '   SELECT attname::text       FROM pg_class, pg_constraint, pg_attribute       WHERE pg_class.oid = conrelid       AND contype=''p''       AND attrelid = pg_class.oid       AND attnum = ANY (conkey)       AND relname=$1;   '   LANGUAGE sql VOLATILE STRICT; 

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org 
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Tuesday, May 17, 2005 4:49 AM
> To: Juan Pablo Espino
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Returning the name of a primary key 
> 
> Juan Pablo Espino  writes:
> > I need to write a function that retrieve the name of at least one 
> > table primary key, if it exists.  The only argument passed to the 
> > function is the table name.  I have thought something like this:
> 
> You need to be searching the list of indexes, not the 
> attributes per se.
> ATExecDropNotNull() might be a useful example.
> 
> 			regards, tom lane
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index 
> scan if your
>       joining column's datatypes do not match
> 
>