Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

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

SELECT               
  pg_attribute.attname, 
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute 
WHERE 
  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.

share|improve this question

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.