sql - 从函数返回时复合数据类型缺少值

Question

我有一个返回 setof 复合数据类型的函数。当我运行该函数时，复合数据类型中定义的两列中只有一列包含数据。在对问题进行故障排除时，我对复合数据类型执行了 RAISE NOTICE，它的列中的数据在结果集中缺少数据。我在 RETURN NEXT 之前和之后都进行了测试。我正在使用 PostgreSQL 9.2。
知道为什么结果集缺少数据吗？

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;

Answer 1

确保你像这样调用函数:

SELECT * FROM get_map_data( ... );

不是这样的:

SELECT get_map_data( ... );

或者您将整个复合类型作为单个列。

可能的解释

UUIDs can be written with enclosing curly braces ({}) in text representation.这恰好与使用相同大括号的文字数组常量的语法相匹配。这个字面量可以转换为 uuid 和 uuid[] 类似:

'{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}'

这可能会引起歧义。只是推测，但我可以想象有些客户会对语法规则感到困惑，并将元素和数组连接到单个数据。

连这个SQL Fiddle遇到 JDBC 错误! (虽然同样适用于整数数据。)

更好的选择

无论哪种方式，您的函数中都有很多毫无意义的努力。循环、取消嵌套和重建数组，...
整个 shebang 可以用这个简单且更快的查询代替:

SELECT m.lid, tiles
FROM   map_layers  m
JOIN   layer_tiles l USING (lid)
WHERE  m.mid = $mid_val;

如果需要，将其包装到一个 SQL 函数中:

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;