Submitted on: 03-23-2001

Description:

Retrieves a comma delimited list of attribute names making up the primary key of the relation passed as argument $1

Code:

CREATE FUNCTION get_pk (text) RETURNS text AS '

-----------------------------------------------------------------

-- Function: get_pk

-- Purpose: Retrieves a comma delimited

-- list of attribute names

-- making up the primary key

-- of the relation passed as argument $1

-- Licence: BSD

-- Author: Joseph Conway

-----------------------------------------------------------------

DECLARE

l_pk RECORD;

l_pk_list text;

l_cntr int4;

l_relname ALIAS FOR $1;

BEGIN

l_pk_list := '''';

l_cntr := 0;

FOR l_pk IN

SELECT pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.relname = l_relname AND pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = ''t''

union all

SELECT pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.relname = l_relname AND pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[1] = pg_attribute.attnum AND pg_index.indisprimary = ''t''

union all

SELECT pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.relname = l_relname AND pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[2] = pg_attribute.attnum AND pg_index.indisprimary = ''t''

union all

SELECT pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.relname = l_relname AND pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[3] = pg_attribute.attnum AND pg_index.indisprimary = ''t''

union all

SELECT pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.relname = l_relname AND pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[4] = pg_attribute.attnum AND pg_index.indisprimary = ''t''

union all

SELECT pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.relname = l_relname AND pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[5] = pg_attribute.attnum AND pg_index.indisprimary = ''t''

union all

SELECT pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.relname = l_relname AND pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[6] = pg_attribute.attnum AND pg_index.indisprimary = ''t''

union all

SELECT pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.relname = l_relname AND pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[7] = pg_attribute.attnum AND pg_index.indisprimary = ''t''

LOOP

if l_cntr > 0 then

l_pk_list := l_pk_list || '','';

end if;

l_pk_list := l_pk_list || l_pk.attname;

l_cntr := l_cntr + 1;

END LOOP;

return l_pk_list;

END;

' LANGUAGE 'plpgsql';