Short-circuit Postgres query after specific value has been returned-CodePudding

Currently have a query of the form:

SELECT myFunc(col1) FROM myTable;

myFunc returns a bool value. Is it possible to short-circuit query execution so that the query will end the first time myFunc returns a row with the value true? It doesn't matter the order in which the query is executed, simply that one execution of the function has been found to be true.

EXISTS doesn't work since it only tests whether or not there are rows returned at all; but myFunc always returns a value (true or false), so EXISTS wouldn't be helpful in identifying the above stopping condition.

CodePudding user response：

Try this

select mf 
from (SELECT myFunc(col1) as mf FROM myTable) t 
where mf limit 1;

I think that the optimizer will flatten the query and do exactly what you need.

Or using exists as @JasonGoemaat suggests:

select exists 
(
 select from myTable where myFunc(col1)
);

CodePudding user response：

I think using exists with a where clause and limit on the subquery should be pretty efficient:

SELECT EXISTS (SELECT 1 FROM myTable WHERE myFunc(col1) LIMIT 1);

If you don't mind checking for an empty result set when no instances of myFunc return true then you could leave off the exists check.

I'd be interested to know if putting myfunc(col1) as a computed field in a view and selecting from the view is any faster; but as you don't say what the myFunc is doing it's hard to be sure which method is best without testing.