postgresql：使用游标从一个数据库中提取数据并将其插入到另一个数据库中

发布于 2024-10-22 13:01:16 字数 875 浏览 3 评论 0原文

这是另一种使用光标的算法，但我很难修复它的错误......

CREATE OR REPLACE FUNCTION extractstudent()
RETURNS VOID AS 
$BODY$
DECLARE
    studcur SCROLL cursor FOR SELECT fname, lname, mname, address FROM student;
BEGIN    
    open studcur; 

    Loop
    --fetching 1 row at a time
    FETCH First FROM studcur;
    --every row fetched is being inserted to another database on the local site
    --myconT is the name of the connection to the other database in the local site
    execute 'SELECT * from dblink_exec(''myconT'', ''insert into temp_student values(studcur)'')';
    --move to the next row and execute again
    move next from studcur;
    --exit when the row content is already empty
    exit when studcur is null;
    end loop;

    close studcur;    

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION extractstudent() OWNER TO postgres;

原文

here is another algorithm using cursor but i'm having a hard time fixing its error ...

CREATE OR REPLACE FUNCTION extractstudent()
RETURNS VOID AS 
$BODY$
DECLARE
    studcur SCROLL cursor FOR SELECT fname, lname, mname, address FROM student;
BEGIN    
    open studcur; 

    Loop
    --fetching 1 row at a time
    FETCH First FROM studcur;
    --every row fetched is being inserted to another database on the local site
    --myconT is the name of the connection to the other database in the local site
    execute 'SELECT * from dblink_exec(''myconT'', ''insert into temp_student values(studcur)'')';
    --move to the next row and execute again
    move next from studcur;
    --exit when the row content is already empty
    exit when studcur is null;
    end loop;

    close studcur;    

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION extractstudent() OWNER TO postgres;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

云朵有点甜 2024-10-29 13:01:16

您很少需要在 postgresql 或 pl/pgsql 中显式使用游标。您所编写的内容看起来很像 SQL Server 游标循环构造，但您不需要这样做。另外，您可以使用“PERFORM”而不是“EXECUTE”来运行查询并丢弃结果：这将避免每次重新解析查询（尽管它无法避免 dblink 每次解析查询）。

你可以做更多这样的事情：

DECLARE
  rec student%rowtype;
BEGIN
  FOR rec IN SELECT * FROM student
  LOOP
    PERFORM dblink_exec('myconT',
      'insert into temp_student values ('
          || quote_nullable(rec.fname) || ','
          || quote_nullable(rec.lname) || ','
          || quote_nullable(rec.mname) || ','
          || quote_nullable(rec.address) || ')');
  END LOOP;
END;

You rarely need to explicitly use cursors in postgresql or pl/pgsql. What you've written looks suspiciously like a SQL Server cursor loop construct, and you don't need to do that. Also, you can use "PERFORM" instead of "EXECUTE" to run a query and discard the results: this will avoid re-parsing the query each time (although it can't avoid dblink parsing the query each time).

You can do something more like this:

DECLARE
  rec student%rowtype;
BEGIN
  FOR rec IN SELECT * FROM student
  LOOP
    PERFORM dblink_exec('myconT',
      'insert into temp_student values ('
          || quote_nullable(rec.fname) || ','
          || quote_nullable(rec.lname) || ','
          || quote_nullable(rec.mname) || ','
          || quote_nullable(rec.address) || ')');
  END LOOP;
END;

回复收藏 0 原文