How to obtain primary key value in trigger function if primary key column name is unknown?

user_1567785 0
Viewed 3048

I'm using postgres. Let's say I'm going to create following trigger:

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

I want to use this trigger with many tables and not every table has primary key with name 'id' (table can have no 'id' column at all). So I need to find out in some way how to use primary key in my trigger function no matter which column name it has. How can I achieve this?

postgresql sql
2 Answers

I took Michel Milezzi's answer and cleaned it up so it only returns what we need, with better performance in a more concise statement. Tested on Postgres v12.1. I imagine it would work on 9.4+.

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
    DECLARE
        col TEXT = (
            SELECT attname
            FROM pg_index
            JOIN pg_attribute ON 
                attrelid = indrelid 
                AND attnum = ANY(indkey) 
            WHERE indrelid = TG_RELID AND indisprimary
        );
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES ((row_to_json(NEW) ->> col), current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

First I'm querying for the name of the table's primary key column, and storing it in the col variable. I filter the query by the special variable TG_RELID, which is the object ID of the table which caused the trigger. (See the Postgres Docs.)

Then we can insert into the audit table. All I've changed from the question is new.ID. Now that we know the name of the primary key column, we can convert the new row--the row that will be (or has been) inserted, updated, or deleted--to json, and select the value with the col variable. This should work for both BEFORE and AFTER triggers. If triggering on DELETE, change row_to_json(NEW) to row_to_json(OLD).

user_81498370
Related