Search Postgresql Archives

Problem with selecting arrays in set-returning plpgsql function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I am trying to select arrays in a set-returning function, but receiving
the error: "array value must start with "{" or dimension information".

This issue appears to relate to locationnodes.rs_people_c sometimes
having an empty array. The return type into which
locationnodes.rs_people_c is returned is INTEGER[].

Assistance much appreciated.
Rory

function:
    CREATE OR REPLACE FUNCTION fn_matview_location_slots (                              
        week_start  DATE,     
    ) RETURNS setof matview_location_slots_info AS                                      
    $$      
    DECLARE 
        resulter    matview_location_slots_info%ROWTYPE;                                
    BEGIN   
        FOR resulter IN 
            SELECT 
                rs_node               AS node,                                          
                rs_date               AS dater,
                ...
                COALESCE(rs_people_c, '{}'::INTEGER[]) AS people
            FROM
                locationnodes
            WHERE
                rs_date >= week_start
        LOOP
            RETURN NEXT resulter;
        END LOOP;
    END; $$ LANGUAGE plpgsql;

type:
    CREATE TYPE matview_location_slots_info AS (                                        
            node              VARCHAR,
            dater             DATE,
            ...
            people            INTEGER[]
    );

data:
    select rs_people_c from locationnodes;
                           rs_people_c                     
    ---------------------------------------------
     {}
     {}
     {}
     {40}
     {28}
     {}
     {1}
     {}
     {36}
     {731}
     {32}
     {31}
     {66}
     {}
     {}
     {}
     {62}
     {540,72,69,53,37,42,201,65,560,51,58}
     {64}

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux