From: | Alexander Reichstadt <lxr(at)mac(dot)com> |
---|---|
To: | David Johnston <polobo(at)yahoo(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] SELECT …. WHERE id is in pool of ids of subquery…… |
Date: | 2012-09-26 21:04:02 |
Message-ID: | BA173FBB-C812-43B0-A85F-DB58E1633765@mac.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Heureka, it works.
Thanks!
Am 26.09.2012 um 22:37 schrieb David Johnston:
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2012-09-26 21:11:19 | Re: [GENERAL] Memory issues |
Previous Message | Shiran Kleiderman | 2012-09-26 21:00:56 | Re: [GENERAL] Memory issues |