From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Reece Hart <reece(at)harts(dot)net> |
Cc: | SF PostgreSQL <sfpug(at)postgresql(dot)org> |
Subject: | Re: IN with functions as subqueries |
Date: | 2006-05-12 17:05:21 |
Message-ID: | 20060512170520.GA24642@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2006-05-12 19:13:28 | South Bay Meeting June? |
Previous Message | Reece Hart | 2006-05-12 16:32:54 | IN with functions as subqueries |