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

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

例如:

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

寻找这样的东西:

%> {select * from foobar where foo=? and bar=? or baz=? , 'foo','bar','baz' };
134567 次浏览
小开
最佳答案

你可使用 -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
小开

在 psql 中，有一个通过

\set name val

命令，该命令应该绑定到 -v name=val命令行选项。引用是很痛苦的，在大多数情况下，将整个查询内容放入 shell here-document 中更容易。

剪辑

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

小开

看来，你问 不能直接从命令行完成。您可以在 plpgsql 中使用用户定义的函数，或者从脚本语言调用查询(后一种方法使得避免 SQL 注入变得更加容易)。

小开

在 PostgreSQL 中发现，你可以像在脚本语言中一样使用 ABc3。遗憾的是，您仍然不能使用 ?，但是可以使用 $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;
小开

您还可以在 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 代码文件中，将 v1标记添加到 WHERE 子句中或 SQL 中的任何其他位置。请注意，这些标记也可以在 open SQL 语句中使用，而不仅仅是在文件中。将其保存为 test.sql:

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

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

感谢 EnterpriseDB 的 Dave Page 提供了最初的提示脚本。

小开

我想提供另一个答案，灵感来自@malCook 的评论(使用 bash)。

如果您需要 使用 -c标志时在查询中使用 shell 变量，这个选项可能对您有用。具体来说，我希望获取一个表的计数，该表的名称是一个 shell 变量(在使用 -c时不能直接传递该变量)。

假设您有您的 shell 变量

$TABLE_NAME='users'

然后您可以通过使用

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)

我会注意到，在这里字符串(<<<操作符)的 echo可能是不必要的，我原本以为引号本身会很好，也许有人可以澄清的原因。

小开

我最后用了一个更好的@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');

这样，您总是可以按照这种顺序执行它(只有在尚未准备的情况下才准备查询) ，重复执行并从最后一个查询获得结果。


提交答案