Re: Arrays, placeholders, and column types

Dan Sugalski <dan(at)sidhe(dot)org> writes:
> At 2:37 PM -0400 10/25/04, Tom Lane wrote:
>> 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)

Ideally it should be, but we haven't yet figured a reasonably clean way
to do it. The problem is that the type assignment is made bottom-up,
and only if it's still unknown when we get up to the INSERT level can we
use the INSERT column types to affect it. As a comparison point, if
you tried

INSERT ... VALUES($1 + $2)

you'd get a complaint about being unable to choose a plus operator,
even though you might think the system ought to infer that from the
datatype of the destination column.

> Is there any particular speed advantage to casting over setting
> paramTypes, or vice versa?

I doubt it would make any visible difference. Do what seems easiest for
your client code.

regards, tom lane