ERROR: Missing FROM-clause entry for table "new"?

Question

Hello, I’m making the following mistake:

ERROR: Missing FROM-clause entry for table "new" CONTEXT: Error occurred on dblink Connection named "unnamed": could not execute command.

SQL statement "select dblink_exec( 'host=... port=5432 dbname=**user = ** password = **' 'INSERT INTO test.teste_replica ("id", "name") values (new.id, new.name)')" PL/pgsql Function teste_bd() line 26 at SQL statement}

and that’s my line of code that’s got the bug:

ELSIF (TG_OP = 'INSERT') THEN
            select 
            dblink_exec( 
            'dbname=postgres 
            hostaddr=***.**.**.***
            user=*** 
            password=*** 
            port=5432',
            '--INSERT INTO teste.teste_replica SELECT NEW.*;
            INSERT INTO teste.teste_replica ("id", "nome") 
            values (NEW."id",NEW."nome")');
            RETURN NEW;

Remembering that both are test databases, where I need to replicate the data after Insert, update and delete from server A to server B, but this error is preventing me, know how I can fix?

Answer 1

Try:

ELSIF (TG_OP = 'INSERT') THEN
            select 
            dblink_exec( 
            'dbname=postgres 
            hostaddr=***.**.**.***
            user=*** 
            password=*** 
            port=5432',
            concat('INSERT INTO teste.teste_replica ("id", "nome") values (', NEW."id", ',', quote_literal(NEW."nome"), ')');
            RETURN NEW;