1

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?

Vishnu G S
  • 654
  • 2
  • 10
  • 27

2 Answers2

5

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
2

Try to put the array and type initialization into a string, maybe you can then get around the problems with the obfuscation layer (aka ORM):

select update_status(cast('{"(1,true)", "(2,false)"}' as status_record[]));

I don't know Hibernate, so I can't tell if that will work.