Postgresql: Problem with selecting arrays in set-returning plpgsql function

Question

I am trying to select arrays in a set-returning function in postgres 8.4, 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. I've tried to get around that with a COALESCE statement. No luck.

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,
            rs_location           AS location
        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[],
        location          INTEGER[]
);

data

select rs_people_c from locationnodes;
           rs_people_c
-------------------------------------
 {}
 {}
 {731}
 {32}
 {31}
 {}
 {62}
 {540,72,69,53,37,42,201,51,58}
 {64}

What version you have ? I checked that on 9.0.4 and it works well (without COALESCE too). Also you have typo in your PL/pgSQL function week_start DATE, should be just week_start DATE. — Grzegorz Szpetkowski
– Grzegorz Szpetkowski, Commented Aug 2, 2011 at 21:21
@Grzegorz : thanks for the notes. I've edited the example above, which is part of a larger function to fix the week_start comma problem. Thanks for pointing that out. I've also added the postgres version (8.4). — rorycl
– rorycl, Commented Aug 2, 2011 at 21:34

rorycl · Accepted Answer · 2011-08-02 21:51:18Z

1

I made a stupid type definition error (which I excluded from my original question, but Grzegorz alluded to -- thanks Grzegorz).

I posting this as an answer for anyone who googles the problem of array value must start with "{" or dimension information.

In my case the issue was that the location return type was defined as an array of integers, but the function was returning a simple integer. Unfortunately Postgres doesn't provide more information about the specific problem in these cases.

Also note that in this sort of case you don't need to coalesce NULL arrays to {} as I did in my question.

In short : make sure your array return types are receiving array input!

answered Aug 2, 2011 at 21:51

rorycl

1,38412 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grzegorz Szpetkowski · Accepted Answer · 2011-08-02 21:53:05Z

In addition to @rorycl's answer here is working test-case under PostgreSQL 8.4.8:

DROP TYPE IF EXISTS matview_location_slots_info;
CREATE TYPE matview_location_slots_info AS
(
    node varchar,
    dater date,
    people integer[]
);
DROP TABLE IF EXISTS locationnodes;
CREATE TABLE locationnodes
(
    rs_node varchar,
    rs_date date,
    rs_people_c integer[]
);
INSERT INTO locationnodes VALUES
    ('aaa', '2011-01-01', '{}'),
    ('bbb', '2011-01-02', '{}'),
    ('ccc', '2011-01-03', '{731}'),
    ('ddd', '2011-01-04', '{32}'),
    ('eee', '2011-01-05', '{31}'),
    ('fff', '2011-01-06', '{}'),
    ('ggg', '2011-01-07', '{62}'),
    ('hhh', '2011-01-08', '{540, 72, 69, 53, 37, 42, 201, 51, 58}'),
    ('iii', '2011-01-09', '{64}');

PL/pgSQL 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,
            rs_people_c AS people
        FROM
            locationnodes
        WHERE
            rs_date >= week_start
    LOOP
        RETURN NEXT resulter;
    END LOOP;
END; $$ LANGUAGE plpgsql;

Result:

SELECT fn_matview_location_slots('2011-01-01');
             fn_matview_location_slots
---------------------------------------------------
 (aaa,2011-01-01,{})
 (bbb,2011-01-02,{})
 (ccc,2011-01-03,{731})
 (ddd,2011-01-04,{32})
 (eee,2011-01-05,{31})
 (fff,2011-01-06,{})
 (ggg,2011-01-07,{62})
 (hhh,2011-01-08,"{540,72,69,53,37,42,201,51,58}")
 (iii,2011-01-09,{64})
(9 rows)

Collectives™ on Stack Overflow

Postgresql: Problem with selecting arrays in set-returning plpgsql function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related