我可以将一个变量通过$1传递给PostgreSQL中的"NOTIFY“命令吗？

Question

我有以下可用的代码：

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;

现在，我想将一个字符串传递给我的sql代码：

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;

但是这给了我这个错误：

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

我不明白我的错误:我尝试了一个简单的SELECT，它起作用了。但我不能在NOTIFY上使用$1。你有什么想法或者方法通过plpython通知一个自定义字符串吗？(我的代码应该传递一个带有更新值的强制转换字典，所以我真的需要首先使用plpython，然后使用NOTIFY)

Answer 1

我最终找到了使用string的format方法的变通方法。

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;