Re: SELECT …. WHERE id is in pool of ids of subquery……

On Sep 26, 2012, at 16:28, Alexander Reichstadt <lxr(at)mac(dot)com> wrote:

> 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

"SETOF record" is likely not what you want since ID is a single value not a "record".

The data type of ID is what you want to return a SETOF of.

You also want to use IN, not EXISTS. You generally only use EXISTS with a correlated sub-query but in this case the sub-query is constant.

David J.