PostgreSQL: How to pass parameters from command line?

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.

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;

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.

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