How to log whole query statement to relation?

From: "David Flegl" <ml_flegl(at)centrum(dot)cz>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: How to log whole query statement to relation?
Date: 2007-04-24 07:00:54
Message-ID: 200704240900.19371@centrum.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi to all,
I realy need help of you. My problem is $subj. I mean, for example. If user run command like:
INSERT INTO public.foo (at1,at10) VALUES ('val1','val10')
I would need to catch this (may be in trigger?) and store this textual command to other log table. Later I will need to run EXECUTE on this. That's why logging.
I tryied to build a trigger, but it's not generally usable, becase some user may run INSERT stmt with others specified atributes (some of them have default values) and trigger cannot catch this situation.

Or anyone has other possible solution how to solve this problem? Generaly: on specified tables I need to know (inside PostgreSQL, not seeing log files in system) which command user run. This is because of I need to made of very simplified asynchronnous multimaster replication (better say synchronization).

Thank's to all contributions...
David F

I create something like this:
--------CUT HERE-----
CREATE OR REPLACE FUNCTION ftgr_data_all() RETURNS trigger AS $$
DECLARE
fname text;
BEGIN
fname:=quote_ident(TG_TABLE_SCHEMA)||'.'||quote_ident(TG_TABLE_NAME);

IF (TG_OP = 'INSERT') THEN
INSERT INTO rep.repl_log (id,op,cmd)
VALUES (NEW.id,'INS',
'INSERT INTO '||fname||' '||
'VALUES ('||quote_literal(NEW.id)||','
||quote_literal(NEW.at1)||','
||quote_literal(NEW.at2)||','
||quote_literal(NEW.at3)||','
||quote_literal(NEW.at4)||')'
);
ELSEIF (TG_OP = 'UPDATE') THEN
ELSEIF (TG_OP = 'DELETE') THEN
END IF;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message A. Kretschmer 2007-04-24 09:35:27 Re: How to log whole query statement to relation?
Previous Message Jasbinder Singh Bali 2007-04-21 23:53:15 Re: call pl/pgsql stored functions from ecpg?