From: sramsay@uga.edu
To: pgsql-sql@postgresql.org
Subject: Re: functions in WHERE clause
Date: Sun, 5 Mar 2006 13:49:03 -0500
Message-ID: <20060305184903.GA11613@lachesis.english.uga.edu> (raw)
In-Reply-To: <10317.1141582600@sss.pgh.pa.us>
References: <20060305151652.GA14216@cantor.english.uga.edu>
<20060305172635.GA77911@winnie.fuhr.org>
<20060305174827.GA14433@cantor.english.uga.edu>
<10317.1141582600@sss.pgh.pa.us>
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
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: sramsay@uga.edu
Subject: Re: functions in WHERE clause
In-Reply-To: <20060305184903.GA11613@lachesis.english.uga.edu>
* 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