将复合类型的数组传递给存储过程 [英] 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)
...但是实际上,它返回的是
相反,请写:
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屋!