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';
|