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

Oh, also, regarding using a view... I need the SQL statement to pass a
variable to the function, which I'm not sure I can do with a view.

So my example would be more like:
CREATE FUNCTION myTest(text) RETURNS SETOF oid AS 'SELECT id FROM foo
WHERE name = $1;' LANGUAGE SQL;
and
SELECT * FROM foo WHERE id in in (myTest("bar"));

Thanks,

Drew

On Wednesday, April 16, 2003, at 04:58 PM, Drew Wilson wrote:

> I have to insert/update/delete into these tables. If I use views, I'd
> have to write rules to handle the write-through operations.
>
> I'd like to avoid that extra code.
>
> Drew
>
> On Friday, May 16, 2003, at 04:54 PM, Dennis Gearon wrote:
>
>> a view instead of the function?
>>
>> 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 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.)
>>> Thanks,
>>> Drew
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 6: Have you searched our list archives?
>>> http://archives.postgresql.org
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to
>> majordomo@postgresql.org
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>