将复合类型的数组传递给存储过程

问题描述：

我可能在形成文字时做了一些错误。假设我有这样一个简单的存储过程：将复合类型的数组传递给存储过程

CREATE OR REPLACE FUNCTION do_something(input_array composite_type[]) 
    RETURNS SETOF text AS 
$BODY$ 
DECLARE 
    temp_var composite_type; 
BEGIN 

    FOR temp_var IN SELECT unnest(input_array) LOOP 
     return next temp_var.message; 
    END LOOP; 

END 
$BODY$ 
    LANGUAGE plpgsql;

的composite_type定义为：

CREATE TYPE composite_type AS 
    (message text, 
    amount numeric(16,2));

执行查询这样的：

SELECT * FROM do_something('{"(test,11)","(test2,22)"}')

产生以下结果集：

(test,11.00) 
(test2,22.00)

相反的：

test 
test2

是不是有毛病我的文字还是应该以不同的方式访问message场？感谢您的任何建议。

SELECT * FROM do_something(ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[]);

而且：

很好的问题！ –

答

如何指定你的输入作为相同的行为与行和数组构造函数的语法观察显示正常，

SELECT ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[];

生产：

'{"(test,11.00)","(test2,22.00)"}'

如果您添加一个：

RAISE NOTICE '!%!',temp_var;

循环内的输出是：

NOTICE: !("(test,11.00)",)! 
NOTICE: !("(test2,22.00)",)!

表明你实际上得到与“信息”为你所期望的元组文字和空的“量”的元组。

所以。为什么？

这是一个微妙的一点。您正在使用：

SELECT unnest(input_array)

这似乎做你想要什么，右：

regress=>  SELECT unnest(ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[]); 
    unnest  
--------------- 
(test,11.00) 
(test2,22.00) 
(2 rows)

...但实际上，它的返回composite_type类型的单列。相反，PL/PgSQL复合类型分配需要每列类型的列一个。所以这个单一的col被推入'message'中，并且没有第二个col。

你应该写：

SELECT * FROM unnest(input_array)

解压复合体对分配。然后，它按预期工作：

regress=> SELECT * FROM do_something(ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[]); 
do_something 
-------------- 
test 
test2 
(2 rows)

如果composite_type的第一个字段是一个非文本类型，你会得到的是相当关于这个更多的信息错误。

优秀的解释。这是plpgsql中一个相当不幸的设计。接错了。（我正在运行相同的一组测试，你击败了我:)） –

@ErwinBrandstetter是的，倾向于同意它是边界错误。 –

@CraigRinger：它不是bug，它的特点是:( - 它是两个事实的结果：Postgres支持嵌套复合类型 - 任何人都应该小心嵌套的级别; plpgsql是类型顺从的，并使用晚期文本强制转换 - 因此任何东西都会转换为文本，然后转换为目标类型 - 但您丢失了有关类型的元数据。 –

答

Craig很好地解释了这种行为的原因 - FOR语句内的赋值变量=值期望为零嵌套。所以，你应该做的事情：

CREATE OR REPLACE FUNCTION do_something(input_array composite_type[]) 
RETURNS SETOF text AS $BODY$ 
DECLARE 
    temp_var record; 
BEGIN 
    -- unnesting 
    FOR temp_var IN SELECT (unnest(input_array)).* 
    LOOP 
     RETURN NEXT temp_var.message; 
    END LOOP; 
    RETURN; 
END 
$BODY$ LANGUAGE plpgsql;

或 - 最好 - 新使用SetReturnedFunction内部 “列清单”

CREATE OR REPLACE FUNCTION do_something(input_array composite_type[]) 
RETURNS SETOF text AS $BODY$ 
DECLARE 
    temp_var record; 
BEGIN 
    -- SELECT FROM 
    FOR temp_var IN SELECT * FROM unnest(input_array) 
    LOOP 
     RETURN NEXT temp_var.message; 
    END LOOP; 
    RETURN; 
END 
$BODY$ LANGUAGE plpgsql;

将复合类型的数组传递给存储过程

相关推荐