Update between 2 databases using dblink not workin

问题:

Basically I have 2 databases each containing 2 tables. I need to update what's in database 2 using a table in database 1.

Below is database 1 table 2 (db1 ta2)

pid  |   pname  |  pcompany  |  clocation  |  did  |   name
 1       Pirates    Argos        London        1       Mary

Database 2 table 1 (db1 tb1)

pid  |   pname        |  pcompany  |
 1       Ba Ba Black      Argos       

Now I need to update 'Ba Ba Black' to Pirates meaning I need to update db2 tb1 from db1 ta2.

UPDATE tb1 
SET name = ta1.name
WHERE ta1.name = (SELECT ta1.name FROM dblink('port=5432, dbname=db1 user=postgres  password=12345',
'SELECT name FROM ta1'))
AS t1 (a integer, b character(20), c integer)
WHERE pid = 1;

But I have this error:

ERROR:  syntax error at or near "AS"
LINE 5: AS t1 (a integer, b character(20), c integer)

Any idea what I might be messing up?

回答1:

Add table / column aliases and (most probably) a WHERE condition like this:

UPDATE tb1 b
SET    name = a.pname   -- maybe you want to update *pname* instead?
FROM  (
   SELECT *
   FROM   dblink('port=5432 dbname=db1 user=postgres  password=12345'
               , 'SELECT pname FROM ta1 WHERE pid = 1')
          AS t(pname text)
   ) a
WHERE b.pid = 1;

Assuming type text. Adapt to your actual type.
And no comma in the connection string.
I am not linking a to b since a returns a single row here.

回答2:

I worked the following way:

DO$$
DECLARE
 sql_statement TEXT;
BEGIN
 sql_statement:='UPDATE public.ta2 SET name='''||NEW.name||''' WHERE pid=1;';
PERFORM public.dblink('port=5432 dbname=db1 user=postgres  password=*',sql_statement);
RETURN NEW;
END;
$$
languaje plpgsql;

You can consult this link: https://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html