How to log whole query statement to relation?

Поиск
Список
Период
Сортировка
От David Flegl
Тема How to log whole query statement to relation?
Дата
Msg-id 200704240900.19371@centrum.cz
обсуждение исходный текст
Ответы Re: How to log whole query statement to relation?  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Re: How to log whole query statement to relation?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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
torun 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
specifiedatributes (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
asynchronnousmultimaster 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;


В списке pgsql-novice по дате отправления:

Предыдущее
От: Tasneem Memon
Дата:
Сообщение: Help needed regarding the PGSQL Source code Download.
Следующее
От: "A. Kretschmer"
Дата:
Сообщение: Re: How to log whole query statement to relation?