Scott Marlowe wrote:
On Tue, 2006-08-08 at 12:49, Patrice Beliveau wrote:
Hi,
I have a query that use a function and some column test to select row.
It's in the form of:
SELECT * FROM TABLE
WHERE TABLE.COLUMN1=something
AND TABLE.COLUMN2=somethingelse
AND function(TABLE.COLUMN3,TABLE.COLUMN4) > 0;
The result of the function does NOT depend only from the table, but also
from some other tables.
Since it's long to process, I've add some output to see what's going on.
I find out that the function process every row even if the row should be
rejected as per the first or the second condition. Then , my question
is: Is there a way to formulate a query that wont do all the check if it
does not need to do it ? Meaning that, if condition1 is false then it
wont check condition2 and that way the function will only be called when
it's really necessary.
What version of postgresql are you running? It might be better in later
versions. The standard fix for such things is to use a subquery...
select * from (
select * from table where
col1='something'
and col2='somethingelse'
) as a
where function(a.col3,a.col4) > 0;
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Thanks for the answer, but it does not work, maybe I did something wrong
First, I'm using version 8.1.3
This is what I did:
select * from (
select * from sales_order_delivery
where sales_order_id in (
select sales_order_id from sales_order
where closed=false
)
) as a where outstandingorder(sales_order_id, sales_order_item,
date_due) > 0;
Some output that I've create look like
INFO: so:03616 soi:1 date:1993-12-23
INFO: so:09614 soi:1 date:1998-06-04
which are the three arguments passed to the function "outstandingorder",
but sales_order 03616 and 09614 are closed.
What's wrong ??
Thanks
