Re: IN with functions as subqueries

On Fri, May 12, 2006 at 09:32:54AM -0700, Reece Hart wrote:
> SFpug-
>
> I occasionally want to form a condition like "fk in (<set of pks>)",
> in which the set of pks is generated by a function. I
> serendipitously discovered how to make this work, but I don't like
> serendipity (er, I do like it, but it's difficult to rely on). I'm
> hoping that someone has a cogent explanation of what I discovered.
>
>
> Obviously, I can hard code set values, such as:
> select * from tbl where fk in (1,2,4);
>
> Extending this, I wrote a function to return the set:
> rkh(at)csb-dev=> create function test_set() returns setof integer
> language sql as $_$ select 1 union select 2 union select 4 $_$;
> CREATE FUNCTION

But once you've done that, you can use it as though it were a table
and do JOINs to it :)

> So, the lesson is that I need to use 'SELECT fx()' rather than fx()
> itself. Why? I understand that IN's signature is something like <expr>
> IN (<subq>), but this explanation seems a little hollow. Is there a
> better way to do this?

Yep.

SELECT t.a, t.b, t.c /* 'SELECT *' is evil :P */
FROM
table t
JOIN
srf('blarg') AS s /* returns one column called t_id */
ON (s.t_id = t.id);

HTH :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!