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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2006-05-12 17:05:21 | Re: IN with functions as subqueries |
Previous Message | David Fetter | 2006-05-10 20:53:04 | [JOB] Linux Engineer, Safeway |