Обсуждение: Returning the name of a primary key

Hello all

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:

char *
give_pkey(char * table_char)TupleDesc        tupdesc;Form_pg_attribute    att;Form_pg_index        ind;int
  i, c=0, temp=-1; 
tupdesc = (TupleDesc) RelationNameGetTupleDesc(tabla_char);
       ind = <<something that idicates which table is>>       for (i=0; i<(tupdesc->natts); i++)       {           att
=tupdesc->attrs[i];           c = c + 1; 
           /* Something that can compare each attribute to determine
if it is a primary key ?*/           if ((ind->indisprimary) && (temp=-1))           {                temp = c;
      att = tupdesc->attrs[temp];            }         }         return pstrdup(NameStr(att->attname)); 
}

Sorry, I don't have much experience in c programming, thanks in
advance for any suggestions, regards,

Juan P. Espino

On Mon, May 16, 2005 at 01:25:46PM -0500, Juan Pablo Espino wrote:

> 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:

Why mess around with a C function, if you can do it in straight SQL?
You can write a SQL function if need be.

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos / con todos los humanos acabaré (Bender)

Juan Pablo Espino <jp.espino@gmail.com> 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

On Mon, May 16, 2005 at 02:35:03PM -0400, Alvaro Herrera wrote:
> On Mon, May 16, 2005 at 01:25:46PM -0500, Juan Pablo Espino wrote:
> 
> > 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:
> 
> Why mess around with a C function, if you can do it in straight SQL?
> You can write a SQL function if need be.

http://lnk.nu/cvs.pgfoundry.org/2op.sql is an example of how to get the
info in pure SQL.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

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 <jp.espino@gmail.com> 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
>
>