blocks|key|567925|text|你可以使用-v结构，例如|type|unstyled|depth|inlineStyleRanges|entityRanges|data|567926|psql+-v+v1=12++-v+v2="'Hello+World'"+-v+v3="'2010-11-12'"|code-block|syntax|javascript|567927|然后引用sql中的变量:v1、:v2等|567928|select+*+from+table_1+where+id+=+:v1;|567929|请注意我们如何使用双引号"+'...'+"传递字符串/日期值|offset|length|style|CODE|567930|entityMap^0|0|0|0|0|C|9|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

You can use the -v construct e.g

<pre><code>psql -v v1=12 -v v2="'Hello World'" -v v3="'2010-11-12'"
</code></pre>

and then refer to the variables in sql as :v1, :v2 etc

<pre><code>select * from table_1 where id = :v1;
</code></pre>

Please pay attention on how we pass string/date value using two quotes <code>" '...' "</code>

blocks|key|784177|text|在PostgreSQL中，您可以像在脚本语言中一样使用PREPARE+statements。不幸的是，您仍然不能使用?，但是您可以使用$n表示法。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|784178|使用上面的示例：|784179|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;|code-block|syntax|javascript|784180|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/interactive/sql-prepare.html^0|R|7|1M|1|1V|2|R|I|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@$9|11|A|12|1|13]]|E|$]]|$1|F|3|G|5|6|7|14|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|15|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

Found out in PostgreSQL, you can <a href="http://www.postgresql.org/docs/current/interactive/sql-prepare.html" rel="noreferrer"><code>PREPARE</code> statements</a> just like you can in a scripting language. Unfortunately, you still can't use <code>?</code>, but you can use <code>$n</code> notation.
<h3>Using the above example:</h3>
<pre><code>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;
</code></pre>

blocks|key|567982|text|在psql中有一种机制，通过|type|unstyled|depth|inlineStyleRanges|entityRanges|data|567983|\set+name+val|code-block|syntax|javascript|567984|命令，该命令应该与-v+name=val命令行选项绑定。引用是痛苦的，在大多数情况下，在这里将整个查询放在一个shell+-document中更容易。|offset|length|style|CODE|567985|编辑|567986|哦，我应该说-v而不是-P+(用于格式化选项)之前的回答是正确的。|567987|entityMap^0|0|0|9|B|0|0|6|2|B|2|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|R|$]]

In psql there is a mechanism via the

<pre><code>\set name val
</code></pre>

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

<h3>Edit</h3>

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

blocks|key|568121|text|您还可以在psql命令行或批处理文件中传入参数。第一条语句收集连接到数据库所需的详细信息。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|568122|最后的提示要求输入约束值，该值将用于WHERE+column+IN()子句中。记住，if字符串要用单引号引起来，并用逗号分隔：|568123|@echo+off
echo+"Test+for+Passing+Params+to+PGSQL"
SET+server=localhost
SET+/P+server="Server+[%25server%25]:+"

SET+database=amedatamodel
SET+/P+database="Database+[%25database%25]:+"

SET+port=5432
SET+/P+port="Port+[%25port%25]:+"

SET+username=postgres
SET+/P+username="Username+[%25username%25]:+"

SET+/P+bunos="Enter+multiple+constraint+values+for+IN+clause+[%25constraints%25]:+"
ECHO+you+typed+%25constraints%25
PAUSE
REM+pause
"C:\Program+Files\PostgreSQL\9.0\bin\psql.exe"+-h+%25server%25+-U+%25username%25+-d+%25database%25+-p+%25port%25+-e+-v+v1=%25constraints%25+-f+test.sql|code-block|syntax|javascript|568124|现在，在SQL代码文件中，在WHERE子句中或在SQL中的任何其他位置添加v1标记。请注意，这些标记也可以在open+SQL语句中使用，而不仅仅是在文件中使用。将此文件另存为test.sql：|568125|SELECT+*+FROM+myTable
WHERE+NOT+someColumn+IN+(:v1);|568126|在Windows中，将整个文件另存为DOS文件(.bat)，将test.sql保存在同一目录中，然后启动批处理文件。|568127|感谢EnterpriseDB的戴夫·佩奇提供的原始提示脚本。|568128|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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:

<pre><code>@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
</code></pre>

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:

<pre><code>SELECT * FROM myTable
WHERE NOT someColumn IN (:v1);
</code></pre>

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.

blocks|key|784120|text|看起来你问can't+be+done+directly+from+the+command+line的问题。您必须在plpgsql中使用用户定义的函数，或者从脚本语言调用查询(后一种方法更容易避免SQL注入)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|784121|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query^0|5|18|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

It would appear that what you ask <a href="https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query">can't be done directly from the command line</a>. 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).

blocks|key|784262|text|我最终使用了一个更好的@vol7ron答案版本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|784263|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');|code-block|syntax|javascript|784264|这样，您始终可以按此顺序执行它(仅当查询尚未准备时才准备查询)，重复执行并获得最后一个查询的结果。|784265|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I've ended up using a better version of @vol7ron answer:
<pre><code>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');
</code></pre>
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.

I have a somewhat detailed query in a script that uses <code>?</code> 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 <code>?</code> with actual values, instead I'd like to pass the arguments after the query.

Example:

<pre><code>SELECT * 
FROM foobar
WHERE foo = ?
 AND bar = ?
 OR baz = ? ;
</code></pre>

Looking for something like:

<pre><code>%&gt; {select * from foobar where foo=? and bar=? or baz=? , 'foo','bar','baz' };
</code></pre>

PostgreSQL: How to pass parameters from command line?

PostgreSQL

数据库

Windows

在使用?占位符的脚本中，我有一个比较详细的查询。我想直接从psql命令行(在脚本之外)测试同样的查询。我希望避免进入并将所有?替换为实际值，而是希望在查询之后传递参数。示例：SELECT  * FROM    foobarWHERE   foo = ?   AND  bar = ?    OR  baz = ?  ;查找类似以下内容：%> {select * from foobar where f

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

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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