将 dblink 生成的记录推送到调用存储过程之外

发布于 2024-12-22 22:18:34 字数 862 浏览 4 评论 0原文

是否可以简单地将 dblink() 生成的结果记录作为调用函数的结果向上传递，例如：

create function execute_generic_sql_on_remote_databases(_sql text) 
return set of record
language plpgsql
as $$
declare
  r record; -- this is already not a real record
begin
  -- get connections to several DBs
  ... 
  -- send _sql queries to the DBs
  ... 
  -- get results of query execution
  loop through connections
    for r in select MAGIC from dblink_get_results(conn, _sql) loop
      return next r;
    end loop;
  end loop;
  -- close connections
  ... 
end;
$$;

实际上可以像 dblink 一样调用此函数：

select * from execute_generic_sql_on_remote_databases('SELECT 1') as r(i int);

但它需要一些MAGIC。 :(

使用 plProxy 可以轻松做到这一点，但问题是如何使用 dblink 来做到这一点，如果可以使用 plpgsql代码>.

原文

Is it possible to simply pass the resulting records generated by dblink() up, as a result of the calling function, something like:

create function execute_generic_sql_on_remote_databases(_sql text) 
return set of record
language plpgsql
as $
declare
  r record; -- this is already not a real record
begin
  -- get connections to several DBs
  ... 
  -- send _sql queries to the DBs
  ... 
  -- get results of query execution
  loop through connections
    for r in select MAGIC from dblink_get_results(conn, _sql) loop
      return next r;
    end loop;
  end loop;
  -- close connections
  ... 
end;
$;

One could call this function like dblink actually:

select * from execute_generic_sql_on_remote_databases('SELECT 1') as r(i int);

But it needs some MAGIC. :(

It is possible to do that using plProxy easily, but the question is how to do it with dblink, if it is possible at all with plpgsql.

分享到QQ

分享到微博

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

发布评论

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

烈酒灼喉 2024-12-29 22:18:34

这是可能，但操作起来有点尴尬。您需要提供一个列定义列表作为函数的附加参数并构建和构建执行动态SQL。对于函数调用本身，您需要再次提供相同的列定义列表：

CREATE OR REPLACE FUNCTION f_generic_dblink(text, text) 
RETURNS SETOF record AS
$body$
BEGIN
-- $1 .. sql -String to be executed on remote server
-- $2 .. column type definition string like 'a int, b text'

RETURN QUERY EXECUTE '
SELECT *
FROM   dblink(''port=5432 dbname=falter'', $1) AS (' || $2 || ')'
USING $1;

END;
$body$
    LANGUAGE plpgsql;

调用：

SELECT * FROM f_generic_dblink('SELECT 1', 'i int' ) AS k(i int);

SELECT * FROM f_generic_dblink('SELECT 1, ''foo''', 'i int, t text')
                                                AS k(i int, t text);

小心！ $2 容易受到 SQL 注入攻击。

您可能对新的SQL/MED 功能感兴趣。

It is possible, but the handling is a bit awkward. You need to provide a column definition list as additional parameter to the function and build & execute dynamic SQL. For the function call itself you need to provide the same column definition list a second time:

CREATE OR REPLACE FUNCTION f_generic_dblink(text, text) 
RETURNS SETOF record AS
$body$
BEGIN
-- $1 .. sql -String to be executed on remote server
-- $2 .. column type definition string like 'a int, b text'

RETURN QUERY EXECUTE '
SELECT *
FROM   dblink(''port=5432 dbname=falter'', $1) AS (' || $2 || ')'
USING $1;

END;
$body$
    LANGUAGE plpgsql;

Call:

SELECT * FROM f_generic_dblink('SELECT 1', 'i int' ) AS k(i int);

SELECT * FROM f_generic_dblink('SELECT 1, ''foo''', 'i int, t text')
                                                AS k(i int, t text);

Careful! $2 is vulnerable to SQL injection.

You might be interested in the new SQL/MED features.

回复收藏 0 原文

~没有更多了~