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@xxxxxxxxxxxxx> wrote:
"Postgres User" <postgres.developer@xxxxxxxxx> 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!
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
