From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: sramsay@uga.edu
Cc: pgsql-sql@postgresql.org
Subject: Re: functions in WHERE clause
Date: Sun, 5 Mar 2006 11:38:46 -0800 (PST)
Message-ID: <20060305113419.P64418@megazone.bigpanda.com> (raw)
In-Reply-To: <20060305184903.GA11613@lachesis.english.uga.edu>
References: <20060305151652.GA14216@cantor.english.uga.edu>
<20060305172635.GA77911@winnie.fuhr.org>
<20060305174827.GA14433@cantor.english.uga.edu>
<10317.1141582600@sss.pgh.pa.us>
<20060305184903.GA11613@lachesis.english.uga.edu>
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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, sramsay@uga.edu
Subject: Re: functions in WHERE clause
In-Reply-To: <20060305113419.P64418@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox