PostgreSQL: how to pass parameters from the command line?

Question

Do I have a somewhat detailed request in a script that uses placeholders ? . I wanted to test the same query directly from the psql command line (outside the script). I want to avoid typing and replacing everyone ? to actual values, instead I would like to pass arguments after the request.

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

Answer 1

You can use the -v construct, for example:

 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; 

Notice how we pass the string / date value using two quotation marks " '...' "

Answer 2

Found in PostgreSQL, you can PREPARE statements in the same way as in the scripting language. Sorry, you still cannot use ? but you can use the notation $n .

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; 

Answer 3

In psql there is a mechanism through

 \set name val 

which should be bound to the command line parameter -v name=val . The quote is painful, in most cases it is easier to put the entire contents of the request inside the shell here of the document.

Edit

oops, I had to say -v instead of -P (which is for formatting options), the previous answer got this right.

Answer 4

You can also pass parameters on the psql command line or from a batch file. The first instructions collect the necessary data to connect to your database.

The last prompt asks for the constraint values ​​that will be used in the WHERE column IN () clause. Do not forget to specify a single quote, if lines, and separated by a 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 to the WHERE clause or somewhere else in SQL. Note that tokens can also be used in an open SQL statement, and not just in a file. Save this as test.sql:

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

On Windows, save the entire file as a DOS BATch file (.bat), save test.sql in the same directory, and run the batch file.

Thanks for the Dave Page, EnterpriseDB, for the original script request.

Answer 5

It seems that what you are asking for cannot be done directly from the command line . You need to either use a user-defined function in plpgsql, or call a query from a scripting language (and the latter approach makes SQL injection easier).

Answer 6

First, you can build a query string using OS variables, and then pass it to PSQL as follows:

 FOO=FOOVALUE BAR=BARVALUE BAZ=BAZVALUE Q1="select * from foobar where foo=${FOOVALUE} and bar=${BARVALUE} or baz=${BAZVALUE};" PSQL [your connection parameters] -c "${Q1}"