postgresql 如何将参数值添加到 pgadmin sql 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32995923/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add parameter values to pgadmin sql query?
提问by Rodrigo
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?
在 pgadmin3 中，我想使用参数化查询（为了更快的调试，只需从我的 php 文件中复制并粘贴查询）。但我还没有找到添加$1, $2... 参数值的选项。是否可以？
This is the query I'm building in a loop, following the suggestion for NULL testing from here:
这是我在循环中构建的查询，遵循此处的NULL 测试建议：
SELECT EXISTS(SELECT 1
FROM tax
WHERE (addby= or addby<>)
AND (adddate= or adddate<>)
AND ( IS NULL AND nome IS NULL OR nome=)
AND ( IS NULL AND rank IS NULL OR rank=)
AND ( IS NULL AND pai IS NULL OR pai=)
AND ( IS NULL AND valido IS NULL OR valido=)
AND ( IS NULL AND sinonvalid IS NULL OR sinonvalid=)
AND ( IS NULL AND espec IS NULL OR espec=)
AND ( IS NULL AND public IS NULL OR public=)
);
Notice that substitute all parameters by hand is tedious, error-prone and probably (I hope) unnecessary.
请注意，手动替换所有参数既乏味又容易出错，而且可能（我希望）没有必要。
Thanks in advance.
提前致谢。
回答by Gabriel's Messanger
I only know two ways.
我只知道两种方法。
First is to use PREPARED STATEMENT(Example after PostgreSQL Manual):
首先是使用PREPARED STATEMENT（PostgreSQL手册后的示例）：
PREPARE usrrptplan (int) AS
SELECT * FROM users u, logs l
WHERE u.usrid= AND u.usrid=l.usrid AND l.date = ;
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.
PREPARE 创建一个准备好的语句。当执行 PREPARE 语句时，将解析、分析和重写指定的语句。当随后发出 EXECUTE 命令时，将计划并执行准备好的语句。
准备好的语句可以带参数：执行时替换到语句中的值。创建准备好的语句时，按位置引用参数，使用$1、$2等。
准备好的语句仅在当前数据库会话期间持续。当会话结束时，准备好的语句被遗忘，因此在再次使用之前必须重新创建它。
Second is to "find-and-replace" $1, $2, .. etc. by proper values. But you want to avoid this one.
其次是通过适当的值“查找和替换” $1, $2, .. 等。但是你想避免这个。
回答by The Coder
In DBeaver you can use parameters in queries just like you can from code, so this will work:
在 DBeaver 中，您可以像在代码中一样在查询中使用参数，因此这将起作用：
select * from accounts where id = :accountId
When you run the query DBeaver will ask you for the value for :accountId and run the query.
当您运行查询时，DBeaver 会询问您 :accountId 的值并运行查询。
