PL/pgSQL function to return the output of various SELECT queries from different database

Question

I have found this very interesting article: Refactor a PL/pgSQL function to return the output of various SELECT queries from Erwin Brandstetter which describes how to return all columns of various tables with only one function:

CREATE OR REPLACE FUNCTION data_of(_table_name anyelement, _where_part text)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
      'SELECT * FROM ' || pg_typeof(_table_name)::text || ' WHERE ' || _where_part;

END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM data_of(NULL::tablename,'1=1 LIMIT 1');

This works pretty well. I need a very similar solution but for getting data from a table on a different database via dblink. That means the call NULL::tablename will fail since the table does not exists on the database where the call is made. I wonder how to make this work. Any try to connect inside of the function via dblink to a different database failed to get the result of NULL::tablename. It seems the polymorph function needs a polymorph parameter which creates the return type of the function implicit.

I would appreciate very much if anybody could help me.

Thanks a lot

Kind regards Brian

Answer 1

it seems this request is more difficult to explain than I thought it is. Here is a second try with a test setup:

Database 1

First we create a test table with some data on database 1:

CREATE TABLE db1_test
(
  id integer NOT NULL,
  txt text
)
WITH (
  OIDS=TRUE
);
INSERT INTO db1_test (id, txt) VALUES(1,'one');
INSERT INTO db1_test (id, txt) VALUES(2,'two');
INSERT INTO db1_test (id, txt) VALUES(3,'three');

Now we create the polymorph function on database 1:

-- create a polymorph function with a polymorph parameter "_table_name" on database 1
-- the return type is set implicit by calling the function "data_of" with the parameter "NULL::[tablename]" and a where part
CREATE OR REPLACE FUNCTION data_of(_table_name anyelement, _where_part text)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
      'SELECT * FROM ' || pg_typeof(_table_name)::text || ' WHERE ' || _where_part;

END
$func$ LANGUAGE plpgsql;

Now we make test call if everything works as aspected on database 1

SELECT * FROM data_of(NULL::db1_test, 'id=2');

It works. Please notice I do NOT specify any columns of the table db1_test. Now we switch over to database 2.

Database 2

Here I need to make exactly the same call to data_of from database 1 as before and although WITHOUT knowing the columns of the selected table at call time. Unfortunatly this is not gonna work, the only call which works is something like that:

SELECT
*
FROM dblink('dbname=[database1] port=[port] user=[user] password=[password]'::text, 'SELECT * FROM data_of(NULL::db1_test, \'id=2\')'::text)
t1(id integer, txt text);

Conclusion

This call works, but as you can see, I need to specify at least once how all the columns look like from the table I want to select. I am looking for any way to bypass this and make it possible to make a call WITHOUT knowing all of the columns from the table on database 1.

Final goal

My final goal is to create a function in database 2 which looks like

SELECT * from data_of_dblink('table_name','where_part')

and which calls internaly data_of() on database1 to make it possible to select a table on a different database with a where part as parameter. It should work like a static view but with the possiblity to pass a where part as parameter.

I am extremly open for suggestions.

Thanks a lot

Brian