I am having a problem with connection of two databases in different systems.Let me explain clearly.
I have one postgres 8.4 server running in my system and another postgres server running in my friend system
My requirement is whenever ,I do some insertions in a table in my postgres server the same things should be replicated in another postgres server.
These are the steps I followed ---------------------------------------------------------------------------------------------------- 1.Creation of table in a database named "testA" in one system say "sys1"
CREATE TABLE emp ( empname text NOT NULL, salary integer );
2.Creation of table in a database named "testB" in another system say "sys2"
CREATE TABLE emp ( empname text NOT NULL, salary integer );
3.Creation of a Function and Trigger in database "testA" in "sys1"
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$ BEGIN IF (TG_OP = 'INSERT') THEN SELECT * FROM dblink_exec('hostname=sys2ip dbname=testB user=postgres password=postgres','INSERT INTO emp NEW.*'); RETURN NEW; END IF; RETURN NULL; END; $emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit BEFORE INSERT ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
4.The error I got is
ERROR: syntax error at or near "NEW" CONTEXT: Error occurred on dblink connection named "unnamed": could not execute command. SQL statement "SELECT * FROM dblink_exec('dbname=test9','INSERT INTO emp NEW.*')" PL/pgSQL function "process_emp_audit" line 3 at SQL statement
********** Error **********
ERROR: syntax error at or near "NEW" SQL state: 42601 Context: Error occurred on dblink connection named "unnamed": could not execute command. SQL statement "SELECT * FROM dblink_exec('dbname=test9','INSERT INTO emp NEW.*')" PL/pgSQL function "process_emp_audit" line 3 at SQL statement.
