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

Question

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?

Answer 1

What's your PostgreSQL version? You must query PostgreSQL catalog to achieve what you want. See script below:

I'm assuming that your PostgreSQL has JSONB support (9.4+).

CREATE TABLE public.test_table 
(
    id BIGINT NOT NULL,
    a_column TEXT NOT NULL,
    CONSTRAINT test_tablepkey PRIMARY KEY (id)
);

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
DECLARE
    reg_id JSONB; 
    affected_row JSON;   
BEGIN

    IF TG_OP IN('INSERT', 'UPDATE') THEN
        affected_row := row_to_json(NEW);
    ELSE
        affected_row := row_to_json(OLD);
    END IF;

    --Get PK columns
    --You may want to extract this to a SQL function
    WITH pk_columns (attname) AS (
        SELECT 
            CAST(a.attname AS TEXT) 
        FROM 
            pg_index i 
            JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) 
        WHERE 
            i.indrelid = TG_RELID 
            AND i.indisprimary
    )
    SELECT 
        json_object_agg(key, value) INTO reg_id
    FROM 
        json_each_text(affected_row)
    WHERE 
        key IN(SELECT attname FROM pk_columns);

    --Raise collected PK
    RAISE INFO 'PK: %', reg_id;

    --TODO: your insert into audit table goes here
    RETURN NEW;
END;
$example_table$ LANGUAGE plpgsql;

CREATE TRIGGER tg_audit_cadprodu_row AFTER INSERT OR UPDATE OR DELETE
  ON public.test_table FOR EACH ROW EXECUTE PROCEDURE public.auditlogfunc();

--A simple test
INSERT INTO test_table VALUES (CAST((random() * 10000) AS INTEGER), 'Test');

Answer 2

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