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

Question

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

137

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

示例：

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

寻找类似以下内容的东西：

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

- vol7ron

请提供更多的背景信息。这个查询是在一个SQL文件中吗？还是在Perl/Python/Ruby/<插入您喜欢的脚本语言>脚本中？或者是在其他地方？ - user554546

@Jack：我想直接从psql提示符（命令行）中执行此操作。我要从脚本中获取我的代码，但不想经历整个查找/替换的过程。 - vol7ron

@Vol7ron，请查看我下面的答案，其中包含一个psql命令行示例。 - MAbraham1

1

@MAbraham1：不错。我应该在提问时给出更多背景信息。我有很多包含 SQL 的脚本，有时候直接使用自定义值针对数据库进行调试非常有用。我正在寻找一种在 Postgres 中轻松实现此操作的方法，而无需保存额外的文件。 - vol7ron

@Vol7ron，谢谢。我是在考虑批处理作业方面的，但你也应该能够在开放式SQL中使用令牌。如果您喜欢我的答案，请不要忘记投票支持。 - MAbraham1

7个回答

39

在PostgreSQL中，你可以像在脚本语言中一样使用PREPARE语句。不幸的是，你仍然无法使用?，但你可以使用$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;

- vol7ron

@IvanBlack，你是不是还有其他要包含的内容？ :) 在会话结束时自动执行解除分配。 - vol7ron

1

请注意，现在的 foo 正在忙碌中，而当前会话未关闭时，另一个 PREPARE 应该有另一个名称。如果你在 psql 中使用 PREPARE，每次都发明一个新名称是很困难的，而 DEALLOCATE 可以帮助解决这个问题 =) - Ivan Black

这个解决方案在我看来非常好。一个有用的副作用是，你可以轻松地多次调用准备好的语句。 - Yuri

16

在psql中，有一种通过

\set name val

这是一个命令，应该与命令行选项-v name=val相关联。引用很麻烦，在大多数情况下，更容易将整个查询放在shell的here-document中。

编辑

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

- wildplasser

10

你还可以在psql命令行或批处理文件中传递参数。第一条语句收集连接到数据库所需的必要细节。

最后一个提示要求输入约束条件值，这些值将用于WHERE列IN()子句中。记得使用单引号包裹字符串，并用逗号分隔：

@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批处理文件（.bat），将test.sql保存在同一目录中，并启动批处理文件。

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

- MAbraham1

+1 对于 Windows 的示例；尽管大多数 Pg 数据库存在于 *nix 变体中。 - vol7ron

2

你需要在示例的第15行将“SET /P bunos=…”更改为“SET /P constraints=…”。这样就可以正常工作了。 - akristmann

4

我想提供另一个灵感来源于 @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 用于仅打印结果数字，不需要其他格式）

我要注意的是，在这个here-string中（即<<<操作符），echo可能并不是必需的，最初我认为仅带引号就可以了，也许有人可以澄清原因。

- Broper

3

看起来你所要求的无法直接从命令行中完成。你可以使用plpgsql中的用户自定义函数或从脚本语言中调用查询（后一种方法更容易避免SQL注入攻击）。

- user554546

2

我最终采用了@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');

通过这种方式，您可以始终按照这个顺序执行它（只有在查询还没有准备好的情况下才准备查询），重复执行并获取最后一个查询的结果。

- Konard

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gavin · Accepted Answer

您可以使用-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="'don''t do this'"。

更好/更安全的方法是让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