代码之家 › 专栏 › 技术社区 › Augustin Riedinger

Postgresql:错误:查询的结构与使用DbLink的函数结果类型不匹配

dblink sql-function postgresql

0

Augustin Riedinger · 技术社区 · 10 年前

因此,我编写了这个方法,旨在使用dblink查询具有相同结构的另一个远程数据库(灵感来自本文 Specify dblink column definition list from a local existing type 还有这个 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) 我收到此错误:

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

这让我抓狂,因为远程表和本地表 确实有 相同的结构。

如果我只返回查询字符串,我可以 UNION 将其添加到本地表中,效果非常好:

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 即使数据来自远程表,也要接受它?或者返回不同的东西以使其工作?

谢谢

1 回复 | 直到 7 年前

1

Community Dai 7 年前

以下构建基于已接受的答案 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;

请注意,dblink调用的“remote”的选定表/列被强制转换为

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