Re: functions in WHERE clause - Mailing list pgsql-sql

On Sun, 5 Mar 2006 sramsay@uga.edu wrote:

> On Sun, Mar 05, 2006 at 01:16:40PM -0500, Tom Lane wrote:
> > sramsay@uga.edu writes:
> > That would work fine if you said RETURNS SETOF ltree.
> >
> > That should work too, except that you are trying to return a record
> > not an ltree value.  Try "RETURN NEXT tree.ltree".
> >
> > > Because SETOF won't work in a WHERE context.
> >
> > Possibly you need to read the error messages you are getting more
> > closely, because I'm pretty sure whatever it said had nothing to
> > do with either SETOF or WHERE ...
>
> I think it does, actually.  I can write functions that return
> ltrees, records, or sets of ltree, and they'll work in any part of
> the query -- except the WHERE clause.  If the function returns
> anything other than a bool, it complains that the function must
> return a bool.
>
> Which makes sense to me, actually, because the "result" of something
> like:
>
> x = 42 and y = 77
>
> (to quote your earlier example) should be true or false -- not a set
> of rows or records or types.  At least I think.
>
> But let's return to your example for a moment, because it really
> does look like the kind of "rewrite" rule that I want here.
>
> You suggested replacing:
>
> SELECT * from some_table WHERE x = 42 AND y = 77
>
> with
>
> create function mytest(int,int) returns bool as
> $$select $1 = 42 AND $2 = 77$$ language sql;
>
> So you could then do:
>
> SELECT * from some_table WHERE mytest(x,y);
>
> But imagine instead that this function is more generic.  You know
> that you're trying to get something that's equal to x and equal to
> y, but you don't know (until the function is called) what those
> rvalues should be.  In other words, it's the 42 and the 47 that you
> don't know until runtime -- you always know what columns your
> searching on.

Then you need to pass those in as well as something representing the row
that's being tested -- where clauses are filters on rows. The whole row
representation might be better than columns for some cases.

For example:

create table tt1(a int, b int);
create function f1(tt1, int) returns bool as 'select $1.a = $2' language
'sql';

select * from tt1 where f1(tt1, 1);

---

The other option is to do this as a set returning function in the first
place rather than trying to do a wierd where clause thing.

create function f2(int) returns setof tt1 as 'select * from tt1 where a =
$1' language 'sql';
select * from f2(1);