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

Thank you very much. Yes, "select * from foo where id in (select * from
myTest())" is the syntax I was looking for.

On Wednesday, April 16, 2003, at 05:12 PM, Stephan Szabo wrote:
> 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).

Yes, the SQL function is a join spanning 5 tables, as well as an OR
clause to test for a null relationship at the top.

Thanks again,

Drew