From: <sramsay@uga.edu>
Date: Sun, 5 Mar 2006 13:49:03 -0500
To: pgsql-sql@postgresql.org
Subject: Re: functions in WHERE clause
Message-ID: <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>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <10317.1141582600@sss.pgh.pa.us>
User-Agent: Mutt/1.5.11

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.

When I try do something along the lines of what you're doing, I get:

CREATE OR REPLACE FUNCTION xpath(lquery) RETURNS bool AS $$
	SELECT ltree ~ $1;
$$ LANGUAGE SQL;

But I can't even load that function, because it says:

ERROR:  column "ltree" does not exist

And round and round I go . . .

Thanks for the reply,

Steve

-- 
Stephen Ramsay
Assistant Professor
Department of English
University of Georgia
email: sramsay@uga.edu
web: http://cantor.english.uga.edu/
PGP Public Key ID: 0xA38D7B11