How do I get the primary key(s) of a table from Postgres via plpgsql?

Question

Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function?

Answer 1

The query above is very bad as it is really slow.

I would recommend this official version:

http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns

if schema is needed the query is as follows

SELECT                  pg_attribute.attname,    format_type(pg_attribute.atttypid, pg_attribute.atttypmod)  FROM pg_index, pg_class, pg_attribute, pg_namespace  WHERE    pg_class.oid = 'foo'::regclass AND    indrelid = pg_class.oid AND    nspname = 'public' AND    pg_class.relnamespace = pg_namespace.oid AND    pg_attribute.attrelid = pg_class.oid AND    pg_attribute.attnum = any(pg_index.indkey)  AND indisprimary 

Answer 2

To provide a straight bit of SQL, you can list the primary key columns and their types with:

SELECT c.column_name, c.data_type FROM information_schema.table_constraints tc  JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)  JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema   AND tc.table_name = c.table_name AND ccu.column_name = c.column_name WHERE constraint_type = 'PRIMARY KEY' and tc.table_name = 'mytable';