Can I pass a variable through $1 to a "NOTIFY" command in PostgreSQL?

Question

I have the following working code :

CREATE EXTENSION IF NOT EXISTS plpython3u;
UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plpython3u';
CREATE OR REPLACE FUNCTION test_notif_trigger_function() RETURNS trigger AS
$$
updateQuery = """
    NOTIFY updateObject;
"""

plan = plpy.prepare(updateQuery)
plpy.execute(plan)
$$ LANGUAGE plpython3u;

DROP TRIGGER IF EXISTS test_notif_trigger ON columnTest;
CREATE TRIGGER test_notif_trigger 
AFTER UPDATE OF state
ON columnTest
FOR EACH ROW EXECUTE PROCEDURE test_notif_trigger_function();

UPDATE columnTest C
SET state=8
WHERE C.id=1;

I now want to pass a string to my sql code :

CREATE EXTENSION IF NOT EXISTS plpython3u;
UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plpython3u';
CREATE OR REPLACE FUNCTION test_notif_trigger_function() RETURNS trigger AS
$$
updateQuery = """
    NOTIFY updateObject, $1;
"""

plan = plpy.prepare(updateQuery, ["text"])
plpy.execute(plan, ["test"])
$$ LANGUAGE plpython3u;

DROP TRIGGER IF EXISTS test_notif_trigger ON columnTest;
CREATE TRIGGER test_notif_trigger 
AFTER UPDATE OF state
ON columnTest
FOR EACH ROW EXECUTE PROCEDURE test_notif_trigger_function();

UPDATE columnTest C
SET state=8
WHERE C.id=1;

But this gives me this error:

ERROR:  spiexceptions.SyntaxError: syntax error at or near "$1"
LINE 3:  NOTIFY updateObject, $1;

I don't understand my mistake : I tried with a simple SELECT and it worked. But I can't use $1 on NOTIFY. Do you have any idea or a way to notify a custom string through plpython ? (my code is supposed to pass a casted dictionary with updated values so I really need to use plpython at first then use NOTIFY)

Change `"test"` to `["test"]`. See [here](https://www.postgresql.org/docs/current/plpython-database.html) for more information. — Adrian Klaver, Oct 16 '20 at 19:04
It was just a typo, i edited it. I begin to think you can't use $1 if it does not concern a table query... — Krophil, Oct 19 '20 at 07:03

score 0 · Accepted Answer · answered Oct 19 '20 at 07:29

I finally found a workaround by using format method of string.

CREATE EXTENSION IF NOT EXISTS plpython3u;
UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plpython3u';
CREATE OR REPLACE FUNCTION test_notif_trigger_function() RETURNS trigger AS
$$
updateQueryTemplate = """
    NOTIFY updateObject, {value};
"""
updateQuery = updateQueryTemplate.format(value="test")
plan = plpy.prepare(updateQuery)
plpy.execute(plan)
$$ LANGUAGE plpython3u;

DROP TRIGGER IF EXISTS test_notif_trigger ON columnTest;
CREATE TRIGGER test_notif_trigger 
AFTER UPDATE OF state
ON columnTest
FOR EACH ROW EXECUTE PROCEDURE test_notif_trigger_function();

UPDATE columnTest C
SET state=8
WHERE C.id=1;

Can I pass a variable through $1 to a "NOTIFY" command in PostgreSQL?

1 Answers1