ERROR: syntax error at or near "NEW"

--00504502b443793d750472809710
Content-Type: text/plain; charset=ISO-8859-1

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.

--00504502b443793d750472809710
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

HI,

=A0I am having a problem with connection of=A0 two databases in =
different systems.Let me explain clearly.

=A0I have one postgres 8.4=
=A0 server running in=A0 my system and another postgres server running in r>my friend system


My requirement is whenever ,I do some insertions=A0 in a table in my po=
stgres server=A0 the same=A0 things should be replicated
in another post=
gres server.

These are the steps I followed
--------------------=
------------------------------------------------------------ ---------------=
-----

1.Creation of=A0 table in=A0 a database named "testA" in=A0 one s=
ystem say "sys1"

CREATE TABLE emp
(
=A0 empname text=
NOT NULL,
=A0 salary integer
);

2.Creation of=A0 table in=A0 =
a database named "testB" in=A0 another=A0 system say "sys2&q=
uot;




CREATE TABLE emp

(

=A0 empname text NOT NULL,

=A0 salary integer

);

3.Creation of a=A0 Function and Trigger in database "testA&q=
uot; in "sys1"

=A0 CREATE OR REPLACE FUNCTION process_emp_=
audit() RETURNS TRIGGER AS $emp_audit$
  =A0 BEGIN
    =
   IF (TG_OP =3D 'INSERT') THEN

          =A0 SELECT * FROM dblink_exec('hostname=
=3Dsys2ip dbname=3DtestB=A0 user=3Dpostgres password=3Dpostgres','I=
NSERT   INTO   =A0 =A0 =A0        =A0 emp NEW.*');<=
br>          =A0 RETURN NEW;
      =A0 E=
ND IF;
      =A0 RETURN NULL;

  =A0 END;
$emp_audit$ LANGUAGE plpgsql;


CREATE TRIGGER e=
mp_audit
BEFORE INSERT ON emp
  =A0 FOR EACH ROW EXECUTE PROCEDUR=
E process_emp_audit();

4.The error I got is

=A0 ERROR:=A0 syn=
tax error at or near "NEW"

CONTEXT:=A0 Error occurred on dblink connection named "unnamed": =
could not execute command.
SQL statement "SELECT * FROM dblink_exec=
('dbname=3Dtest9','INSERT INTO emp NEW.*')"
PL/pgSQ=
L 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 connect=
ion named "unnamed": could not execute command.
SQL statement =
"SELECT * FROM dblink_exec('dbname=3Dtest9','INSERT INTO e=
mp NEW.*')"

PL/pgSQL function "process_emp_audit" line 3 at SQL statement. >
------------------------------------------------------------ ----------=
-----------------------------------------------------

So,I request y=
ou for any kind of solution for this=A0 problem.


Thanks&Regards,
venkat.





--00504502b443793d750472809710--

Re: ERROR: syntax error at or near "NEW"

--000e0cdf1b5667fd1204729a744a
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Boa Tarde Venkat,

Acredito que o erro est=E1 na sua chamada da função dblink_exec:
SELECT * FROM dblink_exec('hostname=3Dsys2ip dbname=3DtestB user=3Dpostgre=
s
password=3Dpostgres','INSERT INTO emp NEW.*');

Pelo fato de voc=EA estar passando o NEW dentro de aspas, o postgres est=E1
entendendo que =E9 uma string e n=E3o o identificador =FAnico NEW.

On Tue, Sep 1, 2009 at 6:15 AM, Venkat Godditi 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 postgr=
es
> server running in
> my friend system
>
> My requirement is whenever ,I do some insertions in a table in my postgr=
es
> 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 "sys=
1"
>
> 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 =3D 'INSERT') THEN
> SELECT * FROM dblink_exec('hostname=3Dsys2ip dbname=3DtestB
> user=3Dpostgres password=3Dpostgres','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=3Dtest9','INSERT INTO em=
p
> 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=3Dtest9','INSERT INTO em=
p
> 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.
>
>
>
>

--000e0cdf1b5667fd1204729a744a
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Boa Tarde Venkat,

Acredito que o erro est=E1 na sua chamada da fun=
ção dblink_exec:
SELECT * FROM dblink_exec('hostname=3Dsys2ip db=
name=3DtestB=A0
user=3Dpostgres password=3Dpostgres','INSERT INTO emp
NEW.*');

Pelo fato de voc=EA estar passando o NEW dentro de aspa=
s, o postgres est=E1 entendendo que =E9 uma string e n=E3o o identificador =
=FAnico NEW.