Re: [HACKERS] Returning the name of a primary key

[John Hansen]

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: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
 
 

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[HACKERS] Returning the name of a primary key

[Juan Pablo Espino]

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

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

Re: [HACKERS] Returning the name of a primary key

[Alvaro Herrera]

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)

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] Returning the name of a primary key

[Tom Lane]

Juan Pablo Espino [EMAIL PROTECTED] 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

Re: [HACKERS] Returning the name of a primary key

[Jim C. Nasby]

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   [EMAIL PROTECTED] 
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?

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster