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: (view raw, whole thread or download thread mbox)
Lists: 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 $_$;

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

Hmm. Then I accidentally did this:
        rkh(at)csb-dev=> select 3 in (select test_set());
        (1 row)
        rkh(at)csb-dev=> select 4 in (select test_set());
        (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?


Reece Hart,, GPG:0x25EC91A0


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-2017 The PostgreSQL Global Development Group