在 dblink 连接查询中使用数组作为参数会导致错误消息：

Question

我正在使用 Postgresql 8.4，并且正在编写一系列函数，我希望能够在其中传递整数数组，我希望通过 dblink 连接查询对其进行评估。该函数看起来像这样：

--======= Create a function that accepts an array of Top(n) Books and outputs the results to a temp table =======--

CREATE OR REPLACE FUNCTION get_weblog_facts_dblink(VARIADIC l_spids integer[])
    RETURNS setof weblog_fact AS

$BODY$
DECLARE 
    rec weblog_fact%rowtype;
BEGIN
    FOR rec IN SELECT * FROM dblink('dbname=weblog_db port=5432 host=111.111.111.001 user=db_dev password=*****',
    'SELECT         
        id_hash,        
        logfile_id_hash,        
        logentry_timestamp,     
        ip,     
        method,     
        uri,        
        status_code,        
        bytes_transfered,       
        time_taken,     
        referrer,       
        user_agent_type_id,     
        special_id,     
        content_title_id,       
        content_release_id,     
        asset_type_id,      
        encode_type_id,     
        licensor_id,        
        broker_id,      
        campaign_id,        
        subsidized,     
        country_code3,      
        city,       
        postal_code,        
        longitude,      
        latitude,       
        language_id,        
        user_id,        
        tag,        
        app_id,     
        app_seconds,        
        app_cached,     
        cdn_id,     
        resource_name
        FROM weblog_fact WHERE special_id = any(' || array_to_string(l_spids, ',') || ')' )
AS weblog(
    id_hash character varying(40),
    logfile_id_hash character varying(40),
    logentry_timestamp timestamp(6) with time zone,     
    ip cidr,        
    method character varying,       
    uri character varying,      
    status_code integer,        
    bytes_transfered bigint,        
    time_taken bigint,      
    referrer character varying,     
    user_agent_type_id integer,     
    special_id bigint,      
    content_title_id bigint,        
    content_release_id bigint,      
    asset_type_id integer,      
    encode_type_id integer,     
    licensor_id integer,        
    broker_id integer,      
    campaign_id integer,        
    subsidized boolean,     
    country_code3 character(3),     
    city character varying,     
    postal_code character varying,      
    longitude character varying,        
    latitude character varying,     
    language_id character varying,      
    user_id integer,        
    tag character varying(64),      
    app_id integer,     
    app_seconds bigint,     
    app_cached boolean,     
    cdn_id integer,     
    resource_name character varying ) 
    LOOP
        RETURN NEXT rec;
    END LOOP;
    RETURN ;
END
$BODY$
LANGUAGE plpgsql VOLATILE;

但是当我运行这个时：

SELECT * FROM get_weblog_facts_dblink (array[159783,157885,159301,159923,157952,159280,157454,157245,159831,157822])

我收到以下错误：

ERROR:  function get_weblog_facts_dblink(integer[]) does not exist
LINE 1: SELECT * FROM get_weblog_facts_dblink (array[159783,157885,1...
                  ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function get_weblog_facts_dblink(integer[]) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 15

这是我第一次尝试接受数组作为参数的函数，并且我意识到我使 dblink 的问题变得复杂。 dblink select 语句作为独立的工作，但我将 special_id = all(VARIADIC) 更改为 special_id IN (12345,12346,13456) )

任何帮助都非常大赞赏。

Answer 1

您不能使用 plpgsql 变量在字符串文字内进行 dblink - PL/pgSQL 不知道字符串内的变量扩展，如 perl 或 php 那样。第二个问题应该是关键字“VARIADIC” - 因此重命名此参数。

尝试修改代码：

dblink(...,
       'SELECT ... FROM weblog_fact 
          WHERE special_id = any(' || array_to_string(_variadic, ',') || ')'
      ) 
AS weblog(...

您可以使用 RETURN QUERY 语句代替 FOR LOOP RETURN NEXT