From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | sramsay(at)uga(dot)edu |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: functions in WHERE clause |
Date: | 2006-03-05 19:38:46 |
Message-ID: | 20060305113419.P64418@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sun, 5 Mar 2006 sramsay(at)uga(dot)edu wrote:
> On Sun, Mar 05, 2006 at 01:16:40PM -0500, Tom Lane wrote:
> > sramsay(at)uga(dot)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);
From | Date | Subject | |
---|---|---|---|
Next Message | sramsay | 2006-03-05 20:10:43 | Re: functions in WHERE clause |
Previous Message | sramsay | 2006-03-05 18:49:03 | Re: functions in WHERE clause |