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}