It's common to get the change history for a specific row/record so it helps if a new column can be added to the log table which stores the primary key value. I know we can get that from JSON but comes with some performance cost.
Primary key column can be fetched using a SQL like below:
SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE i.indrelid = 'table_name'::regclass AND i.indisprimary;
Then we just need to fetch the column value from the table variable NEW.[PK_col_name].
It's common to get the change history for a specific row/record so it helps if a new column can be added to the log table which stores the primary key value. I know we can get that from JSON but comes with some performance cost.
Primary key column can be fetched using a SQL like below:
SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE i.indrelid = 'table_name'::regclass AND i.indisprimary;Then we just need to fetch the column value from the table variable
NEW.[PK_col_name].