Feature Request: Conditional reading for PGMQ

[yayahc]

I request an enhancement for the PGMQ extension to support conditional message retrieval. This would allow pulling based on specific attributes directly within the pgmq_read function.

eg: Pull messages with a specific type:
SELECT * FROM pgmq_read('my_queue', 30, 1, '{"type": "A"}');

Thank you for considering this feature request.

[ChuckHend]

Hello @yayahc. To clarify, do you mean that {"type": "A"} would be part of the message, correct? not introducing a new "type" attribute to each message record. E.g. for example, two messages in queue like below, filter for any messages that have the type key with value equal to A:

 msg_id | read_ct |          enqueued_at          |              vt               |             message             
--------+---------+-------------------------------+-------------------------------+---------------------------------
      1 |       0 | 2024-08-06 13:35:08.771765+00 | 2024-08-06 13:35:08.774281+00 | {"type": "A", "hello": "world"}
      2 |       0 | 2024-08-06 13:35:30.572968+00 | 2024-08-06 13:35:30.574291+00 | {"type": "B", "hello": "earth"}

SELECT * FROM pgmq.read('my_queue', 30, 1, '{"type": "A"}'); would return just msg_id = 1.

I think this would be a great feature. There have been some offline discussions about having a feature like this for pgmq.delete() and pgmq.archive() in addition to pgmq.read().

@theory , I wonder if any of the jsonpath projects might come in useful here.

[yayahc]

@ChuckHend exactly, it will be awesome to have this feature