remote db procedures/functions
От | Armand Pirvu (home) |
---|---|
Тема | remote db procedures/functions |
Дата | |
Msg-id | EC3FF3C7-B839-4138-B0F8-BEF18BCC3015@gmail.com обсуждение исходный текст |
Список | pgsql-admin |
Hi guys For a project which involves auditing changes in the db, I am looking at storing the changes on a different server . So I looked at writing an audit trigger . If all would be locally I would have gone on something like CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$ BEGIN -- -- Create a row in emp_audit to reflect the operation performed on emp, -- make use of the special variable TG_OP to work out the operation. -- IF (TG_OP = 'DELETE') THEN INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO emp_audit SELECT 'U', now(), user, OLD.*; RETURN NEW; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $emp_audit$ LANGUAGE plpgsql; CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit(); Given the need I looked at plproxy and dblink In plproxy I got CREATE OR REPLACE FUNCTION log_emp_audit( operation text, userid text, empname text, salary integer ) RETURNS VOID AS $$ CONNECT 'dbname=auditdb'; $$ LANGUAGE plproxy; CREATE OR REPLACE FUNCTION do_emp_audit() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'DELETE') THEN PERFORM log_emp_audit('DEL', user, OLD.empname, OLD.salary); ELSIF (TG_OP = 'UPDATE') THEN -- save old and new values PERFORM log_emp_audit('OLD', user, OLD.empname, OLD.salary); PERFORM log_emp_audit('NEW', user, NEW.empname, NEW.salary); ELSIF (TG_OP = 'INSERT') THEN PERFORM log_emp_audit('INS', user, NEW.empname, NEW.salary); END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $$ LANGUAGE plpgsql; CREATE TRIGGER emp_remote_audit AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE do_emp_audit(); Now on dblink I tried like in all local version CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$ BEGIN IF (TG_OP = 'DELETE') THEN PERFORM dblink_connect('dbname=mydb2 port=5432 user=postgres password=hd883XLC'); PERFORM dblink_exec('INSERT INTO emp_audit SELECT ''D'', now(), user, ''OLD.*'''); RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN PERFORM dblink_connect('dbname=mydb2 port=5432 user=postgres password=hd883XLC'); PERFORM dblink_exec('INSERT INTO emp_audit SELECT ''U'', now(), user, ''OLD.*'''); RETURN NEW; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $emp_audit$ LANGUAGE plpgsql; CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit(); I did noticed in the research I did that in plproxy each column value was specified , i.e. old.salary etc etc , as opposedto the local version in which I could use old.* 1 - Can I use the same old.* in plproxy and dblink ? If so how ? 2 - If (1) is false, am I correct to assume that the only way I could achieve my goal using dblink/plproxy would be specifyingeach column individually , i.e. old. salary etc etc ? I am inclined to believe (2) is the way and to some extend might fit better since I can filter what columns I will use Thank you for help Armand
В списке pgsql-admin по дате отправления:
Предыдущее
От: Scott MeadДата:
Сообщение: Re: log_statement vs Statistics Collector (pg_stat_database,, etc)
Следующее
От: Shay CohaviДата:
Сообщение: Fwd: how to decrease the promotion time when performing a multiple failovers.....