Postgresql：存储函数中的 dblink

发布于 2024-11-10 04:33:57 字数 605 浏览 6 评论 0原文

我想将 db_A 中的表 tbl_A 中的前 20 个行插入到 db_B 中的 tbl_B 中。
tbl_A 和 tbl_B 的架构是：

CREATE TABLE <tbl_name> (
 id   serial  PRIMARY KEY,
 int  a,
 int b
);

我有一些与以下查询相关的问题

psql db_A
SELECT dblink_connect("dbname=db_B");
SELECT dblink_open('curse', 'SELECT id, a, b FROM tbl_B');
INSERT INTO tbl_A (SELECT id, a, b FROM dblink_fetch('curse', 20) AS (s_is int, s_a int, s_b int)) RETURNING a;

我可以将以下语句放入存储过程中：
是否可以创建一个由上述三个语句组合而成的存储过程，并创建该过程的预准备语句。

如果有人能评论一下使用游标、在存储过程中使用 dblink 或任何其他更优雅地实现上述方法的做法有多好，我将不胜感激。

原文

I want to insert top 20 ROWS from a table tbl_A in db_A to tbl_B in db_B.
The schema for tbl_A and tbl_B is:

CREATE TABLE <tbl_name> (
 id   serial  PRIMARY KEY,
 int  a,
 int b
);

I have some questions related to following queries

psql db_A
SELECT dblink_connect("dbname=db_B");
SELECT dblink_open('curse', 'SELECT id, a, b FROM tbl_B');
INSERT INTO tbl_A (SELECT id, a, b FROM dblink_fetch('curse', 20) AS (s_is int, s_a int, s_b int)) RETURNING a;

Can I put the following statements in stored procedure:
Is it possible to create a stored procedure of above three statements combined and create a prepared statement of that procedure.

I would be highly grateful if someone can comment on how good a practice is it to use cursor, or using dblink inside stored procedures or any other ways that above is achieved more elegantly.

分享到QQ

分享到微博

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

发布评论

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

哽咽笑 2024-11-17 04:33:57

有更简单的方法：

连接到 db_B 并执行以下命令：

CREATE OR REPLACE FUNCTION dblink(text, text)
RETURNS SETOF record AS
  '$libdir/dblink', 'dblink_record'
  LANGUAGE 'c' VOLATILE STRICT
  COST 1
ROWS 1000;
ALTER FUNCTION dblink(text, text) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION dblink(text, text) TO public; -- or whatever

INSERT INTO tbl_B select * from 
 (SELECT * from dblink('hostaddr=localhost port=5432 dbname=db_A user=postgres password=postgres',
'select id, a, b from tbl_A limit 20 '
)
t(
  id integer,
  a integer,
  b integer
)) as q;

There's much easier way:

Connect to db_B and execute the following:

CREATE OR REPLACE FUNCTION dblink(text, text)
RETURNS SETOF record AS
  '$libdir/dblink', 'dblink_record'
  LANGUAGE 'c' VOLATILE STRICT
  COST 1
ROWS 1000;
ALTER FUNCTION dblink(text, text) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION dblink(text, text) TO public; -- or whatever

INSERT INTO tbl_B select * from 
 (SELECT * from dblink('hostaddr=localhost port=5432 dbname=db_A user=postgres password=postgres',
'select id, a, b from tbl_A limit 20 '
)
t(
  id integer,
  a integer,
  b integer
)) as q;

回复收藏 0 原文

~没有更多了~