From: Armand Pirvu (home) <armand.pirvu@gmail.com>
To: pgsql-admin@postgresql.org
Subject: remote db procedures/functions
Date: Tue, 29 Dec 2015 21:09:49 -0600
Message-ID: <EC3FF3C7-B839-4138-B0F8-BEF18BCC3015@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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 opposed to 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 specifying each 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: armand.pirvu@gmail.com
Subject: Re: remote db procedures/functions
In-Reply-To: <EC3FF3C7-B839-4138-B0F8-BEF18BCC3015@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox