PostgreSQL: How to pass parameters from command line?

Question

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

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 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

Answer 2

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;

Answer 3

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.

Answer 4

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.

Answer 5

I would like to offer another answer inspired by @malcook's comment (using bash).

This option may work for you if you need to use shell variables within your query when using the -c flag. Specifically, I wanted to get the count of a table, whose name was a shell variable (which you can't pass directly when using -c).

Assume you have your shell variable

$TABLE_NAME='users'

Then you can get the results of that by using

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)

I will note that the echo in the here-string (the <<< operator) may not be necessary, I originally thought the quotes by themselves would be fine, maybe someone can clarify the reason for this.

Answer 6

It would appear that what you ask can't be done directly from the command line. 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).

Answer 7

I've ended up using a better version of @vol7ron answer:

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

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.