如何使用update语句和db链接在postgresql中编写游标

I am going to update the table of huge record from one DB to another DB.

我将把一个巨大记录表从一个DB更新到另一个DB。

Here I'm using a cursor function:

这里我使用的是游标功能:

CREATE OR REPLACE FUNCTION Drug("Drug" text)
  RETURNS void AS
$BODY$
DECLARE
   curs refcursor;
   rec record;
BEGIN

OPEN curs FOR EXECUTE 'SELECT * FROM ' || quote_ident("Drug") FOR UPDATE;

LOOP
    FETCH NEXT FROM curs INTO rec;
    EXIT WHEN rec IS NULL;

    RAISE NOTICE '%', rec."Id";

    EXECUTE format('update statement with dblink', tbl)
    USING rec.ctid;
END LOOP;

END
$BODY$  LANGUAGE plpgsql;

Is this correct?... or any other...

这是正确的吗......还是其他任何......

Please suggest...

1 个解决方案

#1

What I know dblink doesn't support cursors - cursors cannot be parameter of query (cursors in PostgreSQL are very careful, usually limited to transaction). So this idea is wrong. You can generate row updates - but it will be slow.

我所知道dblink不支持游标 - 游标不能成为查询参数(PostgreSQL中的游标非常小心,通常仅限于事务)。所以这个想法是错误的。您可以生成行更新 - 但速度很慢。

What you can do. You can use FDW API (foreign data wrappers API) and create permanent link (foreign table to second database). Then you can send a usual UPDATE statement to second database.

你可以做什么。您可以使用FDW API(外部数据包装API)并创建永久链接(外部表到第二个数据库)。然后,您可以将通常的UPDATE语句发送到第二个数据库。

I have two databases db1 and db2. db1 is source database, db2 is target database:

我有两个数据库db1和db2。 db1是源数据库,db2是目标数据库:

-- all is executed on db2
CREATE EXTENSION postgres_fdw;
CREATE SERVER db1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'db1');
CREATE USER MAPPING FOR pavel
   SERVER db1 OPTIONS (user 'pavel');
CREATE FOREIGN TABLE db1_source(a int, b int)
   SERVER db1 OPTIONS (table_name 'source');

Now I have a FDW table db1_source and I can do UPDATE:

现在我有一个FDW表db1_source,我可以做UPDATE:

db2=# UPDATE target SET b = db1_source.b 
         FROM db1_source WHERE target.a =  db1_source.a;

This is most effective way how to do UPDATE based on data in other database

这是如何根据其他数据库中的数据进行UPDATE最有效的方法

#1