postgresql：使用 CURSOR 从一个数据库中提取数据并将它们插入到另一个数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5334645/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql: USING CURSOR for extracting data from one database and inserting them to another
提问by mi ho
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;
回答by araqnid
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).
您很少需要在 postgresql 或 pl/pgsql 中显式使用游标。您编写的内容看起来很像 SQL Server 游标循环构造，您不需要这样做。此外，您可以使用“PERFORM”而不是“EXECUTE”来运行查询并丢弃结果：这将避免每次重新解析查询（尽管它无法避免dblink每次解析查询）。
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;
回答by francs
Why not try it by yourself , according the error, you can try to solve them step by step !
为什么不自己尝试一下，根据错误，您可以尝试逐步解决它们！
