Problem with selecting arrays in set-returning plpgsql function - Mailing list pgsql-general

From Rory Campbell-Lange
Subject Problem with selecting arrays in set-returning plpgsql function
Date
Msg-id 20110802190548.GA6327@campbell-lange.net
Whole thread Raw
Responses Re: Problem with selecting arrays in set-returning plpgsql function  (Rory Campbell-Lange <rory@campbell-lange.net>)
List pgsql-general
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}

pgsql-general by date:

Previous
From: Pedro Sam
Date:
Subject: Re: Timeline Conflict
Next
From: bubba postgres
Date:
Subject: Re: pg_largeobject vs pg_toast_XXXX