Is there an analog to pg_notify that allows (un)listening from a dynamically generated channel name?

All we need is an easy explanation of the problem, so here it is.

I am able to do something like the following in my application:

PERFORM pg_notify((
  SELECT CONCAT('some_functional_prefix_', notify_key)
  FROM some_table
  WHERE lookup_key = my_lookup_key
), '');

This way, I can notify e.g. specific clients (designated by notify_key) which are listening for potentially many types of updates (designated by some_functional_prefix_).

However, there doesn’t appear to be a listen/unlisten analog that allows me to build my desired channel name via string concatenation. My clients know their keys, so they can build channel names before interacting with the database easily enough, but it would be much cleaner in the current design to build channel names in PL/pgSQL. Is this possible?

If it makes any difference, I’m using postgres current as of this writing, which is v13.3.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You would use dynamic SQL, by building a LISTEN/UNLISTEN command string, and passing that string to EXECUTE. Using ‘format’ is useful for constructing the string if you don’t what all characters it might contain.

text_var='something';
execute format('LISTEN %I',text_var);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply