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

Unfortunately I am not the admin of the database and I think creating foreign tables for all external tables on other servers may not the smartest solution in the eyes of the responsible people, even if this may be the best way to go. Dblink instead is a well-known tool which is used very often. So maybe it is possible to get the result of NULL::tablename from a different database in any other way than using a foreign table? — Brian
Thanks but I am not sure If I understand what you mean. This local call does not work: SELECT * FROM data_of(pg_typeof(NULL::tablename), 17); — Brian
Right, that's why you'll have to do it via dblink. What is data_of? Never heard of it. — Laurenz Albe

Brian Brian · Accepted Answer · 2018-10-08T08:53:24

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

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

1 Answers