From: | Alexander Reichstadt <lxr(at)mac(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | SELECT …. WHERE id is in pool of ids of subquery…… |
Date: | 2012-09-26 20:28:08 |
Message-ID: | 69389713-7B38-4207-BCBD-BDBAADE1F224@mac.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I am reading the docu and am looking for an example our explanation as to the difference of setof and arrays.
But maybe this is not even relevant. The reason I am looking for this is because I need to build a function that looks for all records whose ids are in a pool of other records.
Maybe this is not making any sense....
So, here is my function:
defect_usermessageids "refid_userid" bigint setof record sql
Definition
1
2
SELECT DISTINCT messages.id FROM messages
JOIN message_recipients msgrec ON msgrec.refid_messages=messages.id AND (msgrec.refid_qmusers=$1 OR messages.refid_sender=$1)
It returns all the IDs of messages of which a given user, the input arg refid_userid, is either a recipient or a sender.
This works as expected.
Now I'd like to get the messages for these ids, but it doesn't work.
It should be something like
SELECT * FROM messages WHERE ID EXISTS (SELECT defect_usermessageids(2))
But this doesn't work.
Please, can someone enlighten me?
Thanks and regards
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-09-26 20:37:13 | Re: SELECT …. WHERE id is in pool of ids of subquery…… |
Previous Message | hubert depesz lubaczewski | 2012-09-26 20:21:40 | Re: Inaccurate Explain Cost |