I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs. All the primary keys are type serial (auto incrementing integer) and all have the name in the format TablenameID eg so a table with the name "person" will have the serial primary key called "PersonID". The only way I can find to do this is:


  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute 
  pg_class.oid = 'person'::regclass AND
  indrelid = pg_class.oid AND
  pg_attribute.attrelid = pg_class.oid AND 
  pg_attribute.attnum = any(pg_index.indkey)
  AND indisprimary

the disadvantage is the extra time this will add to the creation of the audit logs, from explain (on an empty table)

Nested Loop  (cost=0.01..54.92 rows=1 width=72)
  ->  Nested Loop  (cost=0.00..14.48 rows=1 width=31)
        ->  Seq Scan on pg_index  (cost=0.00..6.20 rows=1 width=31)
              Filter: (indisprimary AND (indrelid = 17606::oid))
        ->  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.00..8.27 rows=1 width=4)
              Index Cond: (oid = 17606::oid)
  ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute  (cost=0.01..40.42 rows=2 width=78)
        Index Cond: ((attrelid = 17606::oid) AND (attnum = ANY ((pg_index.indkey)::smallint[])))
Time: 0.103s

Is there a better / faster way of doing this? The serial autonumber key name can be changed at this stage of the project.

migrated from stackoverflow.com May 3 '13 at 1:07

This question came from our site for professional and enthusiast programmers.

  • Common approaches are to pass the name of the primary key column(s) into the trigger as arguments at CREATE TRIGGER time, or (undesirable IMO) to rely on a naming scheme. The trouble is that for perfection you need to handle the fact that the primary key column can change name or type at any time, even within a transaction, so you need to look it up each time unless you don't allow users to ALTER TABLE. – Craig Ringer Mar 5 '13 at 0:01
  • @CraigRinger Thanks. I don`t want to rely on a naming scheme. The users are not allowed to ALTER TABLE. Does the trigger function need to have the name of the primary key or is there a way of setting this as a variable? Since all the Primary Key fields are serial I automatically get person_PersonID_seq which I want. Another option would be to give all the primary keys the same name eg id and then create the sequences separately? – PCid Mar 5 '13 at 9:20
  • How I handle this is I write a PL/PgSQL procedure that takes the target table name and schema as arguments, looks the primary key or other column of interest up from the system catalogs, and generates a CREATE TRIGGER statement that it executes to create a parameterized trigger. If you want to you could even CREATE FUNCTION a per-table customised trigger function. One example of how this can work is here: wiki.postgresql.org/wiki/Audit_trigger_91plus – Craig Ringer Mar 5 '13 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy