Обсуждение: 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 > >