So I wrote this method that aims at querying to another remote database with the same structure using dblink (inspired from this post Specify dblink column definition list from a local existing type and this one Refactor a 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 receive 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
Which drives me mad, because remote table and local table do have the same structure.
Eg. If I only return the query string, I can UNION
it to 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 force anyelement
to accept this data even if it comes from remote table? Or return something different to make it work?
Thanks
Following builds on 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;
Mind that the selected table/columns of "remote" of the dblink call are cast to the local table at
SELECT (remote::%I).* FROM %I.dblink(%L, %L) AS remote (%s)