Thread: array of composite types - how can I launch this function with an array of composite values
array of composite types - how can I launch this function with an array of composite values
From
"Nikita Koselev"
Date:
I have a composite type, like -- CREATE TYPE requirement_vo AS ( name VARCHAR(20), description VARCHAR(20), requirement_id INT, requirement_type_id INT, priority_id INT, necessity_id INT, lower_limit DOUBLE PRECISION, upper_limit DOUBLE PRECISION ); -- and I have a function -- CREATE OR REPLACE FUNCTION add_position(rs requirement_vo[] ) -- Code like this -- SELECT * FROM add_position('{("name", "descr", "req_id", "req_type_id", "priority_id", "nec_id", 2.1321, 1.2323)}'); -- returns "ERROR: malformed array literal: "{("name", "descr", "req_id", "req_type_id", "priority_id", "nec_id", 2.1321, 1.2323)}" SQL state: 22P02 " So the question is - how can I launch this function with an array of composite values? Documentation (http://www.postgresql.org/docs/8.3/interactive/arrays.html) says "Arrays of ... or composite type can be created." Nikita Koselev
Re: array of composite types - how can I launch this function with an array of composite values
From
Tom Lane
Date:
"Nikita Koselev" <koselev@gmail.com> writes: > SELECT * FROM add_position('{("name", "descr", "req_id", "req_type_id", > "priority_id", "nec_id", 2.1321, 1.2323)}'); Parentheses are not part of the array-literal syntax. You'd need something more like '{"(\"name\", \"descr\", ...)"}' and the backslashes would have to be doubled if this is an old-style string literal. Personally I'd avoid the thicket of quoting rules and build up the value using SQL constructs: array[row('name', 'descr', ...)::requirement_vo, row(...), ...] I think casting the first row() to the desired rowtype is probably sufficient, though it might depend on which PG version you're using. regards, tom lane