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

IN with functions as subqueries

From: Reece Hart <reece(at)harts(dot)net>
To: SF PostgreSQL <sfpug(at)postgresql(dot)org>
Subject: IN with functions as subqueries
Date: 2006-05-12 16:32:54
Message-ID: 1147451574.16807.18.camel@tallac.gene.com (view raw or flat)
Thread:
Lists: sfpug
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

Now let's use it:
        rkh(at)csb-dev=> select 4 in (test_set());
         ?column?
        ----------
         f
         f
         t
        (3 rows)

Hmm. Then I accidentally did this:
        rkh(at)csb-dev=> select 3 in (select test_set());
         ?column?
        ----------
         f
        (1 row)
        
        rkh(at)csb-dev=> select 4 in (select test_set());
         ?column?
        ----------
         t
        (1 row)


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?


Thanks,
Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0


Responses

sfpug by date

Next:From: David FetterDate: 2006-05-12 17:05:21
Subject: Re: IN with functions as subqueries
Previous:From: David FetterDate: 2006-05-10 20:53:04
Subject: [JOB] Linux Engineer, Safeway

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