如何获取函数中一行的主键值

Question

postgres中有一个函数，它发送表中CRUD操作的事件。然而，问题是它只适用于具有作为主键的id列的表。如何更改此项，以便可以使用任何列作为主键？

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SET search_path = PUBLIC,
pg_catalog;
CREATE OR REPLACE FUNCTION psycopg2_pgevents_create_event() RETURNS TRIGGER AS $function$
BEGIN
IF (TG_OP = 'DELETE') THEN
PERFORM pg_notify('psycopg2_pgevents_channel',
json_build_object(
'event_id', uuid_generate_v4(),
'event_type', TG_OP,
'schema_name', TG_TABLE_SCHEMA,
'table_name', TG_TABLE_NAME,
'row_id', OLD.id            -------- this is not always "id"
)::text
);
RETURN NULL;
ELSE
raise notice 'Value: %', NEW;
PERFORM pg_notify('psycopg2_pgevents_channel',
json_build_object(
'event_id', uuid_generate_v4(),
'event_type', TG_OP,
'schema_name', TG_TABLE_SCHEMA,
'table_name', TG_TABLE_NAME,
'row_id', NEW.id,         -------- this is not always "id"
'data', NEW
)::text
);
RETURN NULL;
END IF;
END;
$function$ LANGUAGE PLPGSQL;
SET search_path = "$user",
PUBLIC;

Answer 1

如果您只有一个主键列，并且您只想要它的值，则可以使用提取它

to_jsonb(NEW)->>(
SELECT attname
FROM pg_attribute
JOIN pg_index ON indexrelid = attrelid
WHERE indrelid = TG_RELID
AND indisprimary
)

如果你有多个主键列，和/或你想以{"key":"value"}格式检索PK，你可以使用：

SELECT jsonb_object_agg(attname, to_jsonb(NEW)->attname)
FROM pg_attribute
JOIN pg_index ON indexrelid = attrelid
WHERE indrelid = TG_RELID
AND indisprimary