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

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

\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)

You really only need 2 system tables for that:

• pg_constraint - tells you which columns (by number) belong to the primary key
• pg_attribute - translates the column numbers to column names

Note: the system tables might change between PostgreSQL versions, but it doesn't happen often (actually very rarely if at all). And unlike using the information_schema.table_constraints, you don't need any special permissions, just select on the table. (This was tested in Postgres 10.6)

SELECT string_agg(a.attname, ', ') AS pk
FROM
    pg_constraint AS c
    CROSS JOIN LATERAL UNNEST(c.conkey) AS cols(colnum) -- conkey is a list of the columns of the constraint; so we split it into rows so that we can join all column numbers onto their names in pg_attribute
    INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum
WHERE
    c.contype = 'p' -- p = primary key constraint
    AND c.conrelid = '<schemaname>.<tablename>'::REGCLASS; -- regclass will type the name of the object to its internal oid

Take a look at pg_constraint system table. Or information_schema.table_constraints view if you prefer to stick close to the SQL standard.

For a complete example connect to a DB using psql with the "-E" option and type \d <some_table> - you'll see the actual queries used in describing a table.

SELECT a.attname AS name, format_type(a.atttypid, a.atttypmod) AS type
FROM
    pg_class AS c
    JOIN pg_index AS i ON c.oid = i.indrelid AND i.indisprimary
    JOIN pg_attribute AS a ON c.oid = a.attrelid AND a.attnum = ANY(i.indkey)
WHERE c.oid = 'example'::regclass

Output:

 name |  type  
------+--------
 id   | bigint

Preserving column order using generate_subscripts (based on @Paul Draper's answer):

SELECT
  a.attname,
  format_type(a.atttypid, a.atttypmod) 
FROM
  pg_attribute a
  JOIN (SELECT *, GENERATE_SUBSCRIPTS(indkey, 1) AS indkey_subscript FROM pg_index) AS i
    ON
      i.indisprimary
      AND i.indrelid = a.attrelid
      AND a.attnum = i.indkey[i.indkey_subscript]
WHERE
  a.attrelid = 'your_table'::regclass
ORDER BY
  i.indkey_subscript

This will give you the list of constraints along with the definition.

SELECT
   conrelid::regclass AS table_from,
   conname,
   pg_get_constraintdef ( c.oid )
FROM
   pg_constraint c
   JOIN pg_namespace n ON n.oid = c.connamespace
WHERE
   contype IN ( 'f', 'p ' )
   AND conrelid::regclass::TEXT IN ( 'foo' )

ORDER BY
   conrelid::regclass::TEXT,
   contype DESC