如何将参数值添加到 pgadmin sql 查询? [英] How to add parameter values to pgadmin sql query?

问题描述

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

这是我在循环中构建的查询，遵循 此处 的 NULL 测试建议::>

SELECT EXISTS(SELECT 1来自税WHERE(addby=$1 或 addby<>$1)AND (adddate=$2 或 adddate<>$2)AND ($3 为 NULL AND nome IS NULL OR nome=$3)AND ($4 为 NULL AND rank IS NULL OR rank=$4)AND ($5 为空且 pai 为空或 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 为 NULL AND espec 为 NULL OR espec=$8)AND ($9 为 NULL AND public IS NULL OR public=$9));

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

提前致谢.

解决方案

我只知道两种方法.

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

PREPARE usrrptplan (int) ASSELECT * FROM 用户 u，日志 l哪里 u.usrid=$1 AND u.usrid=l.usrid AND l.date = $2;执行 usrrptplan(1, current_date);

<块引用>

PREPARE 创建一个准备好的语句.当执行 PREPARE 语句时，将解析、分析和重写指定的语句.当随后发出 EXECUTE 命令时，将计划并执行准备好的语句.

准备好的语句可以带参数:执行时替换到语句中的值.创建prepared statement时，按位置引用参数，使用$1、$2等

准备好的语句仅在当前数据库会话期间持续.会话结束时，准备好的语句被遗忘，因此必须重新创建才能再次使用.

其次是通过适当的值查找和替换"$1、$2、..等.但你想避免这个.

In pgadmin3, I would like to use parameterized queries (to faster debugging, just copy & paste the query from my php file). But I haven't found an option to add the values of the $1, $2... parameters. Is it possible?

This is the query I'm building in a loop, following the suggestion for NULL testing from here:

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)
       );

Notice that substitute all parameters by hand is tedious, error-prone and probably (I hope) unnecessary.

Thanks in advance.

解决方案

I only know two ways.

First is to use PREPARED STATEMENT (Example after PostgreSQL Manual):

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.

Second is to "find-and-replace" $1, $2, .. etc. by proper values. But you want to avoid this one.

这篇关于如何将参数值添加到 pgadmin sql 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！