PostgreSQL：如何从命令行传递参数？

发布于 2024-12-04 07:19:20 字数 358 浏览 1 评论 0原文

我在使用 ? 占位符的脚本中有一个比较详细的查询。我想直接从 psql 命令行（脚本之外）测试相同的查询。我想避免进入并将所有 ? 替换为实际值，而是想在查询后传递参数。

示例：

SELECT  * 
FROM    foobar
WHERE   foo = ?
   AND  bar = ?
    OR  baz = ?  ;

寻找类似的东西：

%> {select * from foobar where foo=? and bar=? or baz=? , 'foo','bar','baz' };

原文

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.

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' };

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

绝不放开 2024-12-11 07:19:20

您可以使用-v选项，例如：

$ psql -v v1=12 -v v2="'Hello World'" -v v3="'2010-11-12'"

然后在SQL中引用变量如:v1、:v2等：

select * from table_1 where id = :v1;

请注意我们如何使用两个引号传递字符串/日期值" '...' " 但是这种插值方式很容易发生SQL注入，因为是你负责引用。例如需要包含单引号吗？ -v v2="'不要这样做'"。

更好/更安全的方法是让 PostgreSQL 处理它：

$ psql -c 'create table t (a int, b varchar, c date)'
$ echo "insert into t (a, b, c) values (:'v1', :'v2', :'v3')" \
  | psql -v v1=1 -v v2="don't do this" -v v3=2022-01-01

You can use the -v option e.g:

$ 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:

select * from table_1 where id = :v1;

Please pay attention to how we pass string/date values using two quotes " '...' " But this way of interpolation is prone to SQL injections, because it's you who's responsible for quoting. E.g. need to include a single quote? -v v2="'don''t do this'".

A better/safer way is to let PostgreSQL handle it:

$ psql -c 'create table t (a int, b varchar, c date)'
$ echo "insert into t (a, b, c) values (:'v1', :'v2', :'v3')" \
  | psql -v v1=1 -v v2="don't do this" -v v3=2022-01-01

回复收藏 0 原文

む无字情书 2024-12-11 07:19:20

在PostgreSQL中发现，你可以PREPARE语句< /a> 就像在脚本语言中一样。不幸的是，您仍然无法使用 ?，但可以使用 $n 表示法。

使用上面的例子：

PREPARE foo(text,text,text) AS
    SELECT  * 
    FROM    foobar
    WHERE   foo = $1
       AND  bar = $2
        OR  baz = $3  ;
EXECUTE foo('foo','bar','baz');
DEALLOCATE foo;

Found out in PostgreSQL, you can PREPARE statements just like you can in a scripting language. Unfortunately, you still can't use ?, but you can use $n notation.

Using the above example:

PREPARE foo(text,text,text) AS
    SELECT  * 
    FROM    foobar
    WHERE   foo = $1
       AND  bar = $2
        OR  baz = $3  ;
EXECUTE foo('foo','bar','baz');
DEALLOCATE foo;

回复收藏 0 原文

何以心动 2024-12-11 07:19:20

在 psql 中，有一种通过

\set name val

命令的机制，该机制应该与 -v name=val 命令行选项相关联。引用是痛苦的，在大多数情况下，将整个查询内容放入外壳中的此处文档中会更容易。

编辑

哎呀，我应该说 -v 而不是 -P （用于格式化选项）之前的回复是正确的。

In psql there is a mechanism via the

\set name val

command, which is supposed to be tied to the -v name=val command-line option. Quoting is painful, In most cases it is easier to put the whole query meat inside a shell here-document.

Edit

oops, I should have said -v instead of -P (which is for formatting options) previous reply got it right.

回复收藏 0 原文

你又不是我 2024-12-11 07:19:20

您还可以在 psql 命令行或批处理文件中传递参数。第一个语句收集连接数据库所需的详细信息。

最后的提示要求输入约束值，该值将在 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\9.0\bin\psql.exe" -h %server% -U %username% -d %database% -p %port% -e -v v1=%constraints% -f test.sql

现在，在 SQL 代码文件中，在 WHERE 子句中或 SQL 中的任何其他位置添加 v1 标记。请注意，标记还可以在打开的 SQL 语句中使用，而不仅仅是在文件中。将其另存为 test.sql：

SELECT * FROM myTable
WHERE NOT someColumn IN (:v1);

在 Windows 中，将整个文件另存为 DOS BATch 文件 (.bat)，将 test.sql 保存在同一目录中，然后启动批处理文件。

感谢 EnterpriseDB 的 Dave Page 提供的原始提示脚本。

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.

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:

@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\9.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:

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.

Thanks for Dave Page, of EnterpriseDB, for the original prompted script.

回复收藏 0 原文

泼猴你往哪里跑 2024-12-11 07:19:20

我想提供另一个受 @malcook 评论启发的答案（使用 bash）。

如果您需要在使用 -c 标志时在查询中使用 shell 变量，则此选项可能适合您。具体来说，我想获取一个表的计数，该表的名称是一个 shell 变量（使用 -c 时不能直接传递该变量）。

假设你有你的shell变量

$TABLE_NAME='users'

然后你可以通过使用

psql -q -A -t -d databasename -c <<< echo "select count(*) from $TABLE_NAME;"

（-q -A -t只是打印出结果数字而不需要额外的格式）

来获得结果我会注意到echo （ <<< 运算符）可能不是必需的，我原本以为引号本身就可以了，也许有人可以澄清原因为了这。

I would like to offer another answer inspired by @malcook's comment (using bash).

This option may work for you if you need to use shell variables within your query when using the -c flag. Specifically, I wanted to get the count of a table, whose name was a shell variable (which you can't pass directly when using -c).

Assume you have your shell variable

$TABLE_NAME='users'

Then you can get the results of that by using

psql -q -A -t -d databasename -c <<< echo "select count(*) from $TABLE_NAME;"

(the -q -A -t is just to print out the resulting number without additional formatting)

I will note that the echo in the here-string (the <<< operator) may not be necessary, I originally thought the quotes by themselves would be fine, maybe someone can clarify the reason for this.

回复收藏 0 原文

与往事干杯 2024-12-11 07:19:20

看来您所问的不能直接从命令行。您要么必须在 plpgsql 中使用用户定义的函数，要么从脚本语言调用查询（后一种方法可以更容易地避免 SQL 注入）。

回复收藏 0 原文

Bonjour°[大白 2024-12-11 07:19:20

我最终使用了 @vol7ron 答案的更好版本：

DO $
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_prepared_statements WHERE name = 'foo') THEN
        PREPARE foo(text,text,text) AS
            SELECT  * 
            FROM    foobar
            WHERE   foo = $1
                AND bar = $2
                OR  baz = $3;
    END IF;
END$;
EXECUTE foo('foo','bar','baz');

这样您始终可以按此顺序执行它（仅当尚未准备好时才准备查询），重复执行并获取最后一个查询的结果。

I've ended up using a better version of @vol7ron answer:

DO $
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_prepared_statements WHERE name = 'foo') THEN
        PREPARE foo(text,text,text) AS
            SELECT  * 
            FROM    foobar
            WHERE   foo = $1
                AND bar = $2
                OR  baz = $3;
    END IF;
END$;
EXECUTE foo('foo','bar','baz');

This way you can always execute it in this order (the query prepared only if it does not prepared yet), repeat the execution and get the result from the last query.

回复收藏 0 原文

~没有更多了~