Pushing records produced by dblink out of the calling stored procedure

Question

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.

Answer 1

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.