To: sramsay@uga.edu
cc: pgsql-sql@postgresql.org
Subject: Re: functions in WHERE clause 
In-reply-to: <20060305174827.GA14433@cantor.english.uga.edu> 
References: <20060305151652.GA14216@cantor.english.uga.edu>
	<20060305172635.GA77911@winnie.fuhr.org>
	<20060305174827.GA14433@cantor.english.uga.edu>
Comments: In-reply-to sramsay@uga.edu
	message dated "Sun, 05 Mar 2006 12:48:27 -0500"
Date: Sun, 05 Mar 2006 13:16:40 -0500
Message-ID: <10317.1141582600@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

sramsay@uga.edu writes:
> You can't do this:

> CREATE FUNCTION xpath(lquery) RETURNS ltree AS $$
>   SELECT ltree FROM event WHERE ltree ~ $1;
> $$ LANGUAGE SQL;

That would work fine if you said RETURNS SETOF ltree.

> But I also can't get this kind of thing to work:

> CREATE FUNCTION xpath(lquery) RETURNS SETOF ltree AS $$
> DECLARE
>     tree record;
> BEGIN
> 	FOR tree IN SELECT ltree FROM event WHERE ltree ~ $1 LOOP
> 		RETURN NEXT tree;
> 	END LOOP;
> 	RETURN;
> END
> $$ LANGUAGE plpgsql;

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 ...

			regards, tom lane