Re: "ERROR: Argument of WHERE must not be a set function"?

On Wed, 16 Apr 2003, Drew Wilson wrote:

> I want to use a function to generate a list of OIDs to be used in a
> subselect.
>
> However, I can't figure out what to return from my function that will
> properly work in a WHERE clause.
>
> I tried:
> CREATE FUNCTION myTest() RETURNS SETOF oid AS 'SELECT id FROM foo;'
> LANGUAGE SQL;
>
> But when I try:
> SELECT * FROM foo WHERE id in in (myTest());

I think the syntax would be:
 select * from foo where id in (select * from myTest())

> I get this error message:
> "ERROR: Argument of WHERE must not be a set function"
>
>
> How can I use a function to generate my subselect? (I want to cal my
> function just once, and avoid calling it once per row.)

I think 7.4 might let you get away with calling the function only once for
the above, but current versions don't AFAIK.  I assume the actual
conditions are more complicated than the above (which could probably be
reformulated into a join manually).