Re: the IN clause saga - Mailing list pgsql-jdbc

In case you missed my last comment:

Mind that this is only necessary for backward compatibility.  With 7.4
and the V3 protocol you just send an array and the backend sorts it out.

Of course, one can opt in not providing this feature for pre 7.4
backends but I see no reason for that.  The parsing required is confined
to the surrounding sql fragments (we already split the command) and very
simple.

Fernando

Barry Lind wrote:
> No you are not.
>
> --Barry
>
> Felipe Schnack wrote:
>
>>   Am I the only the only one who doesn't like the idea of the driver
>> parsing SQL statements (to check if there is a IN clause)
>>
>> On Tue, 22 Jul 2003 10:41:22 -0400
>> Fernando Nasser <fnasser@redhat.com> wrote:
>>
>>
>>> Oliver Jowett wrote:
>>>
>>>> On Tue, Jul 22, 2003 at 09:05:45AM -0400, Fernando Nasser wrote:
>>>>
>>>>
>>>>> Thanks for summarizing it Oliver.
>>>>>
>>>>> I've asked Tom Lane about the backend behavior and he informed me
>>>>> that:
>>>>>
>>>>> 1) 7.4 backends do support parameters in the IN predicate, as ($1,
>>>>> $2, $3) (i.e., our (?, ?, ?) syntax).
>>>>>
>>>>> 2) 7.4 backends have a PostgreSQL specific extension that allows
>>>>> you to fill the IN predicate with a list: ($1)  (i.e., our (?) ).
>>>>> One has to pass a PostgreSQL array, like integer[] to fill the
>>>>> list.  Note that the parenthesis is already in place, it is not
>>>>> generated by the ? expansion.
>>>>
>>>>
>>>>
>>>> I assume this is only when you're doing a PREPARE/EXECUTE?
>>>>
>>>
>>> yes.
>>>
>>>
>>>>> The feature 2 in 7.4 backends is of limited use as the planner does
>>>>> not know about the list, so the generated plan will not be as good
>>>>> as if you pass the list with fixed values since the beginning.  But
>>>>> an improvement for this can be attempted for 7.5.
>>>>
>>>>
>>>>
>>>> Hm, then it sounds like the right solution is to have setArray()
>>>> expand as
>>>> the guts of an IN clause when the backend is <7.4 or server prepares
>>>> are
>>>> off, and the parameter is in a query of the form "... IN (?)", and as a
>>>> normal array otherwise.
>>>>
>>>
>>> That is _exactly_ what I am proposing (option 2 of your summary)
>>>
>>>
>>>
>>> --
>>> Fernando Nasser
>>> Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
>>> 2323 Yonge Street, Suite #300
>>> Toronto, Ontario   M4P 2C9
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 2: you can get off all lists at once with the unregister command
>>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>>
>>
>
>
>


--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9