PostgreSQL：如何从命令行传递参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7389416/
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
PostgreSQL: How to pass parameters from command line?
提问by vol7ron
I have a somewhat detailed query in a script that uses ?placeholders. I wanted to test this same query directly from the psql command line (outside the script). I want to avoid going in and replacing all the ?with actual values, instead I'd like to pass the arguments after the query.
我在使用?占位符的脚本中有一个比较详细的查询。我想直接从 psql 命令行（在脚本之外）测试这个相同的查询。我想避免进入并用?实际值替换所有的，而是我想在查询之后传递参数。
Example:
例子：
SELECT *
FROM foobar
WHERE foo = ?
AND bar = ?
OR baz = ? ;
Looking for something like:
寻找类似的东西：
%> {select * from foobar where foo=? and bar=? or baz=? , 'foo','bar','baz' };
回答by Gavin
You can use the -v construct e.g
您可以使用 -v 构造，例如
psql -v v1=12 -v v2="'Hello World'" -v v3="'2010-11-12'"
and then refer to the variables in sql as :v1, :v2 etc
然后将sql中的变量引用为：v1，：v2等
select * from table_1 where id = :v1;
Please pay attention on how we pass string/date value using two quotes " '...' "
请注意我们如何使用两个引号传递字符串/日期值 " '...' "
回答by vol7ron
Found out in PostgreSQL, you can PREPAREstatementsjust like you can in a scripting language. Unfortunately, you still can't use ?, but you can use $nnotation.
在 PostgreSQL 中发现，您可以像在脚本语言中一样使用PREPARE语句。不幸的是，您仍然不能使用?，但您可以使用$n符号。
Using the above example:
使用上面的例子：
PREPARE foo(text,text,text) AS
SELECT *
FROM foobar
WHERE foo =
AND bar =
OR baz = ;
EXECUTE foo('foo','bar','baz');
DEALLOCATE foo;
回答by wildplasser
In psql there is a mechanism via the
在 psql 中有一个机制通过
\set name val
command, which is supposed to be tied to the -v name=valcommand-line option. Quoting is painful, In most cases it is easier to put the whole query meat inside a shell here-document.
命令，它应该与-v name=val命令行选项相关联。引用是痛苦的，在大多数情况下，将整个查询内容放在 shell here-document 中更容易。
Edit
编辑
oops, I should have said -vinstead of -P(which is for formatting options) previous reply got it right.
哎呀，我应该说-v而不是-P（用于格式化选项）之前的回复是正确的。
回答by MAbraham1
You can also pass-in the parameters at the psql command-line, or from a batch file. The first statements gather necessary details for connecting to your database.
您还可以在 psql 命令行或批处理文件中传入参数。第一个语句收集连接到数据库所需的详细信息。
The final prompt asks for the constraint values, which will be used in the WHERE column IN() clause. Remember to single-quote if strings, and separate by comma:
最后一个提示询问约束值，这些值将用于 WHERE 列 IN() 子句中。请记住单引号 if 字符串，并用逗号分隔：
@echo off
echo "Test for Passing Params to PGSQL"
SET server=localhost
SET /P server="Server [%server%]: "
SET database=amedatamodel
SET /P database="Database [%database%]: "
SET port=5432
SET /P port="Port [%port%]: "
SET username=postgres
SET /P username="Username [%username%]: "
SET /P bunos="Enter multiple constraint values for IN clause [%constraints%]: "
ECHO you typed %constraints%
PAUSE
REM pause
"C:\Program Files\PostgreSQL.0\bin\psql.exe" -h %server% -U %username% -d %database% -p %port% -e -v v1=%constraints% -f test.sql
Now in your SQL code file, add the v1 token within your WHERE clause, or anywhere else in the SQL. Note that the tokens can also be used in an open SQL statement, not just in a file. Save this as test.sql:
现在，在您的 SQL 代码文件中，在 WHERE 子句或 SQL 中的任何其他地方添加 v1 标记。请注意，令牌还可以用于打开的 SQL 语句，而不仅仅是文件。将其保存为 test.sql：
SELECT * FROM myTable
WHERE NOT someColumn IN (:v1);
In Windows, save the whole file as a DOS BATch file (.bat), save the test.sql in the same directory, and launch the batch file.
在 Windows 中，将整个文件保存为 DOS BATch 文件 (.bat)，将 test.sql 保存在同一目录中，然后启动批处理文件。
Thanks for Dave Page, of EnterpriseDB, for the original prompted script.
感谢 EnterpriseDB 的 Dave Page 提供原始提示脚本。
回答by MAbraham1
It would appear that what you ask can't be done directly from the command line. You'll either have to use a user-defined function in plpgsql or call the query from a scripting language (and the latter approach makes it a bit easier to avoid SQL injection).
看起来你问的不能直接从命令行完成。您要么必须在 plpgsql 中使用用户定义的函数，要么从脚本语言调用查询（后一种方法可以更容易地避免 SQL 注入）。
