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

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

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

SELECT c.column_name, c.data_typeFROM 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_nameWHERE constraint_type = 'PRIMARY KEY' and tc.table_name = 'mytable';

\d tablename 

will give you the primary key info along with other table related information such as all columns, their types, associated indexes, constraints, rules, triggers etc.You probably don't need all that information, but it is the fastest way to get all details at a glance, see more details here.

It returns something like this:

    Table "public.tablename"     Column |  Type   | Modifiers     --------+---------+-----------     col1   | text    | not null     col2   | numeric |      col3   | text    |      col4   | text    |      col5   | numeric |     Indexes:        "tablename_pkey" PRIMARY KEY, btree (col1)