将复合类型的数组传递给存储过程 [英] Passing array of a composite type to stored procedure

问题描述

我可能在写字面量方面做错了.假设我有一个简单的存储过程，如下所示:

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)",)!

表明您实际上正在获取一个以"message"作为您期望的元组文本的元组，并且为空"amount".

所以.为什么?

有点微妙.您正在使用:

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)

...但是实际上，它返回的是>类型的单列. PL/PgSQL复合类型分配期望每个类型列一个列.因此，单个上校栏将被推送到消息"中，并且没有第二个上校栏.

相反，请写:

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的第一个字段是非文本类型，则您将得到一个错误信息，该信息可以提供更多信息.

I'm probably doing something wrong with forming the literal. Suppose I have a simple stored procedure like this:

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;

The composite_type is defined as:

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

Performing a query like this:

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

Produces this result set:

(test,11.00)
(test2,22.00)

Instead of:

test
test2

Is it something wrong with my literal or should I access the message field in a different way? Thanks for any suggestions.

解决方案

How you specify your input appears fine, as the same behaviour is observed with row- and array-constructor syntax:

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

And:

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

produces:

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

If you add a:

 RAISE NOTICE '!%!',temp_var;

inside the loop the output is:

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

showing that you're actually getting a tuple with "message" as the tuple text you expected and a null "amount".

So. Why?

It's a bit of a subtle one. You're using:

SELECT unnest(input_array)

which seems to do what you want, right:

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

... but actually, it's returning a single column of type composite_type. PL/PgSQL composite type assignment expects one column per type column instead. So the single col is being shoved into 'message' and there is no second col.

Instead, write:

SELECT * FROM unnest(input_array)

to unpack the composite for assignment. Then it works as expected:

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

If the first field of composite_type were of a non-text type, you'd get an error that was rather more informative about this.

这篇关于将复合类型的数组传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！