Re: How to get the primary key fields?

Roberto (SmartBit) wrote:
> Hi all
>
> how could I do a single select resulting a list of field name that are
> primary keys of a table??
>

In PostgreSQL 7.2.x (and I think in 7.1.x) you can use the plpgsql function
get_pk() located here:

http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=36

-----------------------------------------------------------------
-- Function: get_pk
-- Purpose:  Retrieves a comma delimited
--           list of attribute names
--           making up the primary key
--           of the relation passed as argument $1


In 7.3 (now in beta), you can use dblink_get_pkey() from contrib/dblink.
Here's how it looks:

create table foo(f1 int, f2 text, f3 text[], primary key (f1,f2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for
table 'foo'

-- list the primary key fields
select * from dblink_get_pkey('foo');
  position | colname
----------+---------
         1 | f1
         2 | f2
(2 rows)


Joe