Heureka, it works. Thanks! Am 26.09.2012 um 22:37 schrieb David Johnston:
> 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.
