remote db procedures/functions

["Armand Pirvu (home)" <armand.pirvu@xxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin