sql - 如何将参数值添加到 pgadmin sql 查询？

Question

在 pgadmin3 中，我想使用参数化查询(为了加快调试速度，只需从我的 php 文件中复制并粘贴查询)。但我还没有找到添加 $1、$2... 参数值的选项。可能吗？

这是我在循环中构建的查询，遵循来自 here 的 NULL 测试建议:

SELECT EXISTS(SELECT 1
              FROM tax
              WHERE (addby=$1 or addby<>$1)
                    AND (adddate=$2 or adddate<>$2)
                    AND ($3 IS NULL AND nome IS NULL OR nome=$3)
                    AND ($4 IS NULL AND rank IS NULL OR rank=$4)
                    AND ($5 IS NULL AND pai IS NULL OR pai=$5)
                    AND ($6 IS NULL AND valido IS NULL OR valido=$6)
                    AND ($7 IS NULL AND sinonvalid IS NULL OR sinonvalid=$7)
                    AND ($8 IS NULL AND espec IS NULL OR espec=$8)
                    AND ($9 IS NULL AND public IS NULL OR public=$9)
       );

请注意，手动替换所有参数是乏味的、容易出错的，而且可能(我希望)没有必要。

提前致谢。

Answer 1

我只知道两种方法。

首先是使用PREPARED STATEMENT (PostgreSQL 手册后的示例):

PREPARE usrrptplan (int) AS
    SELECT * FROM users u, logs l
    WHERE u.usrid=$1 AND u.usrid=l.usrid AND l.date = $2;

EXECUTE usrrptplan(1, current_date);

PREPARE creates a prepared statement. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed.

Prepared statements can take parameters: values that are substituted into the statement when it is executed. When creating the prepared statement, refer to parameters by position, using $1, $2, etc.

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again.

其次是通过适当的值“查找并替换”$1、$2 等。但是您想避免这种情况。