postgresql - 从函数调用中分配带有 UUID 字段的复杂类型

Question

我正在使用 PostgreSQL 9.3，使用 plpgsql 函数:

CREATE TEMP TABLE uuid_inside (
    id  uuid PRIMARY KEY
);

CREATE FUNCTION uuid_test_func(id uuid)
  RETURNS uuid_inside AS
$$
DECLARE
    res_uuid_inside uuid_inside;
BEGIN
    IF (id = '00000000-0000-0000-0000-000000000001'::uuid) THEN
        SELECT uuid_test_func('00000000-0000-0000-0000-000000000000'::uuid)
            INTO res_uuid_inside;
        RETURN res_uuid_inside;
    END IF;

    res_uuid_inside.id := id;

    RETURN res_uuid_inside;
END;
$$
LANGUAGE plpgsql;

调用:

SELECT uuid_test_func('00000000-0000-0000-0000-000000000001'::uuid);

留言:

ERROR: invalid input syntax for uuid: "(00000000-0000-0000-0000-000000000000)"
SQL-state: 22P02

但这工作正常:

SELECT uuid_test_func('00000000-0000-0000-0000-000000000002'::uuid);

问题不在于递归函数调用 - 原始代码引用了内部的其他函数。

Answer 1

简单的功能

函数中的递归似乎毫无意义。这个简单的 sql 函数完成了这项工作(没有嵌套也没有复合类型包装器):

CREATE FUNCTION uuid_test_func(id uuid)
  RETURNS uuid AS
$func$
SELECT CASE WHEN $1 = '00000000-0000-0000-0000-000000000001'::uuid
                 THEN '00000000-0000-0000-0000-000000000000'::uuid
            ELSE $1
       END
$func$ LANGUAGE plpgsql;

但这可能只是为了简化演示。

解决原文中的问题

至于错误信息。您遇到了 PL/pgSQL 的一个令人困惑的“特性”:
复合类型分配需要每个类型列一列。分配复合类型作为一个整体不是一种选择。

这与 UUID 类型本身无关。

这个修改后的版本可以工作:

CREATE OR REPLACE FUNCTION uuid_test_func(id uuid)
  RETURNS uuid_inside AS
$func$
DECLARE
    res_uuid_inside uuid_inside;
BEGIN
   IF (id = '00000000-0000-0000-0000-000000000001'::uuid) THEN
       SELECT <b>* FROM</b> uuid_test_func('00000000-0000-0000-0000-000000000000'::uuid)
       INTO   res_uuid_inside.id;
  --   INTO   res_uuid_inside;    -- would work, too - 1st col is assigned
       RETURN res_uuid_inside;
   END IF;

   res_uuid_inside.id := id;

   RETURN res_uuid_inside;
END
$func$ LANGUAGE plpgsql;

密切相关的问题:
Passing array of a composite type to stored procedure

更简单的功能

也就是说，我建议使用这种简化形式(保留递归和复合结果):

CREATE OR REPLACE FUNCTION uuid_test_func(id uuid)
  RETURNS uuid_inside AS
$func$
BEGIN
   IF (id = '00000000-0000-0000-0000-000000000001'::uuid) THEN
      RETURN (SELECT f FROM uuid_test_func('00000000-0000-0000-0000-000000000000'::uuid) f);
   ELSE
      RETURN row(id)::uuid_inside;
   END IF;
END
$func$ LANGUAGE plpgsql;