PostgreSQL：存储函数中的dblink [英] Postgresql: dblink in Stored Functions

问题描述

我想从db_A中的表tbl_A插入db_B中的tbl_B的前20个行。

tbl_A和tbl_B的模式为： p $ p> 创建表< tbl_name> （
id串行主键，
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））返回a; 
  

• 我可以在存储过程中放置​​以下语句吗？


• 是否可以创建将上述三个语句组合在一起的存储过程，并为该过程创建准备好的语句。

如果有人可以评论使用游标，或者在存储过程中使用dblink，或者通过其他任何更优雅地实现上述方法的方式，我将不胜感激。

解决方案

还有更简单的方法：

连接到db_B并执行以下操作：

 创建或替换功能dblink（text，text）
 RETURNS SETOF record AS 
'$ libdir / dblink'，'dblink_record'
语言'c'波动率
成本1 
第1000行； 
 ALTER FUNCTION dblink（text，text）所有者对postgres； 
授权执行功能dblink（text，text）公开； -或其他
 
插入tbl_B select * from 
（SELECT * from dblink（'hostaddr = localhost port = 5432 dbname = db_A user = postgres password = postgres'，
 '从tbl_A限制20中选择id，a，b'
）
t（
 id整数，
a整数，
b整数
））作为q; 
  

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.

解决方案

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;

这篇关于PostgreSQL：存储函数中的dblink的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！