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?

Community · Accepted Answer · 2017-05-23 12:15:19Z

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:

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Jan 15, 2015 at 12:40

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 2 · Accepted Answer · 2015-01-15 13:02:40Z

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.

edited Jan 15, 2015 at 13:02

answered Jan 15, 2015 at 12:28

user330315

1 Comment

Vishnu G S Over a year ago

But Hibernate fails to interpret ::status_record. will cast() operator work out?

Collectives™ on Stack Overflow

PostgreSQL: Issue with passing array to procedure

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related