Boa Tarde Venkat,
Acredito que o erro está na sua chamada da função dblink_exec:
SELECT * FROM dblink_exec('hostname=sys2ip dbname=testB user=postgres
password=postgres','INSERT INTO emp NEW.*');
Pelo fato de você estar passando o NEW dentro de aspas, o postgres está
entendendo que é uma string e não o identificador único NEW.
On Tue, Sep 1, 2009 at 6:15 AM, Venkat Godditi <[email protected]> wrote:
> 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.
>
>
>
>
