From: | Venkat Godditi <venkatg(dot)16(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Cc: | Venkat Godditi <venkatg(dot)16(at)gmail(dot)com> |
Subject: | ERROR: syntax error at or near "NEW" |
Date: | 2009-09-01 09:15:29 |
Message-ID: | 9c6ddf9c0909010215k2d6d3066ybc4bacf2139dfae2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
HI,
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.
---------------------------------------------------------------------------------------------------------------------------
So,I request you for any kind of solution for this problem.
Thanks&Regards,
venkat.
From | Date | Subject | |
---|---|---|---|
Next Message | samana srikanth | 2009-09-01 11:17:48 | archive_command |
Previous Message | PESTY Benoit | 2009-09-01 07:15:10 | Re: [ADMIN] Background color in query tool |