PostgreSQL: Issue with passing array to procedure

Question

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?

Answer 1

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

Answer 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.