Postgresql: ERROR: the request structure does not match the type of the result of the function. Using DbLink

Question

So, I wrote this method, which is aimed at querying another remote database with the same structure as dblink (inspired by this post. Indicate the dblink column definition list from a local existing type and this Refactor PL / pgSQL function to return the output of various SELECT queries )

CREATE OR REPLACE FUNCTION select_remote(_table anyelement) RETURNS SETOF anyelement AS $func$ DECLARE _dblink_schema text; _cols text; _server text := 'host=ngrok.com port=45790 user=postgres password=postgres dbname=backup-28-08'; _table_name text := pg_typeof(_table); BEGIN SELECT nspname INTO _dblink_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'dblink' AND e.extnamespace = n.oid; SELECT array_to_string(array_agg(column_name || ' ' || udt_name), ', ') INTO _cols FROM (select column_name, udt_name from information_schema.columns WHERE table_name = _table_name order by ordinal_position) as sub; RETURN QUERY EXECUTE format('SELECT * FROM %I.dblink(%L, %L) AS remote (%s)', _dblink_schema, _server, format('SELECT * FROM %I', _table_name), _cols ); END; $func$ LANGUAGE plpgsql; 

But when I do select * from select_remote(NULL::my_table) , I get this error:

 ERROR: structure of query does not match function result type DETAIL: Returned type character varying does not match expected type character varying(255) in column 2. CONTEXT: PL/pgSQL function select_remote(anyelement) line 18 at RETURN QUERY ********** Erreur ********** ERROR: structure of query does not match function result type État SQL :42804 Détail :Returned type character varying does not match expected type character varying(255) in column 2. Contexte : PL/pgSQL function select_remote(anyelement) line 18 at RETURN QUERY 

This drives me crazy because the remote table and the local table have the same structure.

Eg. If I return only the query string, I can UNION it in the local table, and it works very well:

 SELECT * FROM public.dblink('host=ngrok.com port=45790 user=postgres password=postgres dbname=backup-28-08', 'SELECT * FROM my_table') AS remote (id int4, fname varchar, lname varchar, email varchar, slug varchar) UNION SELECT * FROM my_table 

What am I doing wrong? How can I make anyelement accept this data even if it comes from a remote table? Or return something else to make it work?

thanks

Answer 1

In accordance with the accepted answer to my question :

 CREATE OR REPLACE FUNCTION select_remote(_table anyelement) RETURNS SETOF anyelement AS $func$ DECLARE _dblink_schema text; _cols text; _server text := 'host=ngrok.com port=45790 user=postgres password=postgres dbname=backup-28-08'; _table_name text := pg_typeof(_table); BEGIN SELECT nspname INTO _dblink_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'dblink' AND e.extnamespace = n.oid; SELECT array_to_string(array_agg(column_name || ' ' || udt_name), ', ') INTO _cols FROM (select column_name, udt_name from information_schema.columns WHERE table_name = _table_name order by ordinal_position) as sub; RETURN QUERY EXECUTE format('SELECT (remote::%I).* FROM %I.dblink(%L, %L) AS remote (%s)', _table_name, _dblink_schema, _server, format('SELECT * FROM %I', _table_name), _cols ); END; $func$ LANGUAGE plpgsql; 

Remember that the selected table / columns of the "remote" dblink call are passed to the local table in

 SELECT (remote::%I).* FROM %I.dblink(%L, %L) AS remote (%s)