PostgreSQL:将数组传递给过程的问题 [英] PostgreSQL: Issue with passing array to procedure
问题描述
CREATE TYPE status_record AS
(
id bigint,
status boolean
);
一个用类型数组作为输入参数进行一些处理的过程:
CREATE OR REPLACE FUNCTION update_status(status_list status_record [])
RETURNS文本AS
$ BODY $
DECLARE
BEGIN
- 做一些处理
返回'SUCCESS';
end; $ BODY $
语言plpgsql VOLATILE
COST 100;
最后,我查询过程如下:
<$ p_p>
select *
from update_status(cast(ARRAY [(385,false),(387,false)] as status_record []));
在pgadmin中一切正常。后来,当我尝试使用 Hibernate原生SQL查询Ka Boom !!!调用相同的命令时显示以下内容:
org.postgresql.util.PSQLException:
错误:数组值必须以{或维度信息开始
最终问题: ARRAY [ - something]
和 { - something}
做同样的工作? 使用数组字面(数组的文本表示形式),因为数组构造函数 ARRAY [/]
必须由Postgres评估:
SELECT update_status ( '{ (1,T), (2,F)}' :: status_record []);
也许即使没有明确的转换:
SELECT update_status('{(1,t),(2,f)}');
之前在SO上也有类似的情况:
- 传递数组从<数组> node-postgres到plpgsql功能
- 如何将自定义类型数组传递给Postgres函数
I have a type as:
CREATE TYPE status_record AS
(
id bigint,
status boolean
);
A procedure that does some processing with an array of type as input parameter as:
CREATE OR REPLACE FUNCTION update_status(status_list status_record[])
RETURNS text AS
$BODY$
DECLARE
BEGIN
--does some processing
return 'SUCCESS';
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Finally I queried the procedure as:
select *
from update_status(cast(ARRAY[(385,false),(387,false)] as status_record[]));
Everything works fine in pgadmin. Later when I tried to call the same using Hibernate native SQL Query Ka Boom!!! The following is displayed:
org.postgresql.util.PSQLException: ERROR: array value must start with "{" or dimension information
Final question: both ARRAY[--something]
and {--something}
does the same job?
Use an array literal (text representation of the array), since the array constructor ARRAY[...]
has to be evaluated by Postgres:
SELECT update_status('{"(1,t)","(2,f)"}'::status_record[]);
Maybe even without the explicit cast:
SELECT update_status('{"(1,t)","(2,f)"}');
There were similar cases on SO before:
- Pass array from node-postgres to plpgsql function
- How to pass custom type array to Postgres function
这篇关于PostgreSQL:将数组传递给过程的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!