本文介绍了PostgreSQL:存储函数中的dblink的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想从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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!