On Wed, 16 Apr 2003, Drew Wilson wrote:
> 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.
I wasn't worried about the function (per-se) but the usage.
Select * from foo where id in (select id from myTest())
seems to me anyway pretty equivalent (excepting any possible null related
wierdness) to something like:
select * from foo, (select * from myTest()) bar where foo.id=bar.id
Which should only call the function once.
