Skip site navigation (1) Skip section navigation (2)

Re: IN with functions as subqueries

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 (view raw or flat)
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!

In response to

sfpug by date

Next:From: Josh BerkusDate: 2006-05-12 19:13:28
Subject: South Bay Meeting June?
Previous:From: Reece HartDate: 2006-05-12 16:32:54
Subject: IN with functions as subqueries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group