Re: functions in WHERE clause

sramsay(at)uga(dot)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