Re: Arrays, placeholders, and column types

At 2:37 PM -0400 10/25/04, Tom Lane wrote:
>Dan Sugalski <dan(at)sidhe(dot)org> writes:
>> I've got some SQL statements that I'm issuing from my app using the
>> PQexecParams() C call. All the parameters are passed in as literal
>> string parameters (that is, the paramTypes array entry for each
>> placeholder is set to 0) letting the engine convert.
>
>> INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3, $4, $5])
>> DB error is: ERROR: column "xyzzy" is of type numeric[] but
>> expression is of type text[]
>
>The ARRAY[] construct forces determination of the array type, and it
>defaults to text[] in the absence of any type information from the array
>components. (There's been some discussion of allowing the array type
>determination to be postponed further, but we haven't thought of a good
>way to do it yet.) What you'll need to do is specify at least one of
>the array elements to be "numeric", either via paramTypes or with a cast
>in the SQL command:
>
>INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3::numeric, $4, $5])

Hrm. Okay, not a problem. (I was assuming the column type would be
used to type the array, though I can see reasons to not do so) Is
there any particular speed advantage to casting over setting
paramTypes, or vice versa?
--
Dan

--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan(at)sidhe(dot)org have teddy bears and even
teddy bears get drunk