Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sep 26, 2012, at 16:28, Alexander Reichstadt <lxr@xxxxxxx> 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" bigintsetof recordsql
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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux