On Sep 26, 2012, at 16:28, Alexander Reichstadt <l...@mac.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.
