问题描述
因此,我写了这种方法,旨在使用dblink查询具有相同结构的另一个远程数据库(灵感来自本文,然后通过)
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;
但是当我执行 select * from select_remote(NULL :: my_table)
我收到此错误:
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.
例如。如果仅返回查询字符串,则可以将它 UNION
到本地表中,并且效果很好:
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
我在做什么错?如何强制 anyelement
接受这些数据,即使它们来自远程表也是如此?还是返回其他使它正常工作的东西?
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?
谢谢
推荐答案
以下内容基于对:
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;
请注意,dblink调用的远程的选定表/列已强制转换为本地表在
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)
这篇关于PostgreSQL:错误:查询的结构与使用DbLink的函数结果类型不匹配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!