PostgreSQL:LISTEN的参数替换

Question

常识规定，SQL查询字符串不应手动组装。因此，所有数据库接口都提供参数替换，所有用户都使用它，无一例外。*

我使用的是PostgreSQL v10.5，nodejs v8.12.0，node postgres 7.6.1。

参数替换对SELECT语句起到预期的作用：

> await db.query("select from users where id = 'mic'");
(success, 1 row returned)
> await db.query("select from users where id = $1", ["mic"]);
(success, 1 row returned)

但它不适用于LISTEN语句：

> await db.query("listen topicname");
(success)
> await db.query("listen $1", ["topicname"]);
(error: syntax error at or near "$1")

我要听的主题的名称是动态的。它来自半可靠的来源，不应该由用户控制。但是，为什么要违背所有既定的最佳实践并抓住任何机会呢？

不幸的是，从我的测试来看，我担心PostgreSQL根本无法对LISTEN查询进行参数替换。

对此有什么解决方案或变通方法吗？

*)这种说法可能只有在某些乌托邦式的未来社会才是正确的。

Answer 1

我没有足够的声誉来评论答案，但所提出的解决方案对我不起作用。使用%L会产生一个带引号的字符串，这会导致以下错误：

ERROR: syntax error at or near "'topic'"

应该使用%I格式(SQL标识符，这是针对表和列名记录的，但它也适用于通道名称(。您也可以使用quote_ident函数。请参阅此处有关创建动态查询的文档。

以下PL/pgSQL函数适用于我们：

CREATE OR REPLACE FUNCTION listenForChannel(
channel_   TEXT
) RETURNS VOID AS $$
BEGIN
EXECUTE format('LISTEN %I', channel_);
END
$$ LANGUAGE PLPGSQL;

Answer 2

你说得对，这在PostgreSQL中是做不到的。

作为一种变通方法，编写一个使用动态SQL的PL/pgSQL函数，如下所示：

EXECUTE format('LISTEN %L', topicname);

format函数正确地转义字符串；在这种情况下，生成正确引用字符串Literal的%L格式是合适的格式。