I have a function returning a setof
a composite data type. When I run the function, only one of the two columns defined in the composite data type has data in it. When troubleshooting the problem I did a RAISE NOTICE
on the composite data type and it has data in the column that is missing data in the result set. I tested both before and after the RETURN NEXT
. I am using PostgreSQL 9.2.
Any idea why the result set is missing the data?
CREATE TYPE map_data AS (lid uuid, tile_ids uuid [] );
CREATE OR REPLACE FUNCTION get_map_data(mid_val uuid) RETURNS SETOF map_data AS $$
DECLARE
j uuid;
b uuid;
m map_data%rowtype;
tid uuid []; --tile ids
BEGIN
FOR j IN
SELECT lid FROM map_layers WHERE "mid" = mid_val
LOOP
FOREACH b IN ARRAY (SELECT tiles FROM layer_tiles WHERE lid = j)
LOOP
tid := array_append(tid,b);
END LOOP;
SELECT j, tid INTO m;
RETURN NEXT m;
tid := '{}'; --clear the array.
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
Make sure you call the function like this:
SELECT * FROM get_map_data( ... );
And not like this:
SELECT get_map_data( ... );
Or you get the whole composite type as a single column.
UUIDs can be written with enclosing curly braces ({}
) in text representation. This happens to match the syntax for literal array constants, which use the same braces. This literal can be cast to uuid
and uuid[]
alike:
'{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}'
Which might introduce ambiguity. Just speculating, but I could imagine that some clients get confused with the syntax rules and concatenate element and array to a single datum.
Even this SQL Fiddle runs into a JDBC error! (While the same works with integer data.)
Either way, there is a lot of pointless effort in your function. Looping, unnesting and re-building arrays, ...
The whole shebang can be replaced with this simple and much faster query:
SELECT m.lid, tiles
FROM map_layers m
JOIN layer_tiles l USING (lid)
WHERE m.mid = $mid_val;
Wrap it into an SQL function if needed:
CREATE OR REPLACE FUNCTION get_map_data(_mid_val uuid)
RETURNS SETOF map_data AS
$func$
SELECT m.lid, l.tiles
FROM map_layers m
JOIN layer_tiles l USING (lid)
WHERE m.mid = $1
$func$ LANGUAGE sql;