On Feb 20, 2008, at 5:51 PM, Postgres User wrote:
> Now to end my fixation, one last item. What about the case of a null
> or empty param value- is there a way to assign a condition value that
> Postgres will ignore when processing the query?
>
> This syntax results in a seq scan: WHERE fielda = Coalesce(param,
> fielda)
> because it applies only to non-nulls
>
> Is there another way to write this- perhaps using your array syntax on
> an empty array? Basically I'd PG to ignore the condition just as it
> ignores WHERE 1 = 1
Not sure whether you want no rows returned if param is NULL or all
rows (all rows looking at your example), but you could simply check
param for NULL before comparing it, so either:
WHERE param IS NOT NULL AND fielda = param
or
WHERE param IS NULL OR fielda = param
In the second case, if param IS NULL you will get a sequential scan
of course, as that's the most efficient way to return all rows.
>
> On Wed, Feb 20, 2008 at 8:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Postgres User" <postgres.developer@gmail.com> writes:
>>
>>> My users are developers and the goal was to accept a simple
>>> comma-delimited list of string values as a function's input
>>> parameter.
>>> The function would then parse this input param into a valid regex
>>> expression.
>>
>> Why are you fixated on this being a regex? If you aren't actually
>> trying to expose regex capabilities to the users, you'll just be
>> having
>> to suppress a bunch of strange behaviors for special characters.
>>
>> ISTM that the best solution is to use an array-of-text parameter,
>> along the lines of
>>
>> where name = any (array['Smith', 'Jones', ...])
>>
>> For what you're doing, you'd not actually want the array[] syntax,
>> it would look more like
>>
>> where name = any ('{Smith,Jones}'::text[])
>>
>> This should optimize into an indexscan in 8.2 or later.
>>
>> regards, tom lane
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
>
>
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,47c1522f233091890169212!
