Hello,
I want to log with triggers or in functions, and these logs should be
independet of the transaction. Beside i want to have the information
which action was commited and which not.
So my idea was to log into the same database with dblink, return the
primary keys and add them into a commit table.
But my problem is, that I do not now how to write the rule properly.
My schema locks like this:
CREATE TABLE log_msg (
msg_id bigserial not null,
msg text not null,
constraint msg_pkey primary key (msg_id)
);
CREATE TABLE log_commit (
msg_id bigint not null,
constraint msg_pkey primary key (msg_id)
);
CREATE VIEW log AS
SELECT log_msg.*, log_commit.msg_id IS NOT NULL AS commited
FROM log_msg LEFT JOIN log_commit USING (msg_id);
CREATE OR REPLACE RULE "insert_log" AS
ON INSERT TO log DO INSTEAD
-- now this is pseudo code:
INSERT INTO log_commit (msg_id)
SELECT dblink('dbname=samedb', 'INSERT log_msg (msg) VALUES ('
|| quote_literal(new.msg)
|| ') RETURNING msg_id');
Regards,
Gerhard
