PostgreSQL: How to pass parameters from command line?
You can use the -v construct 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 on how we pass string/date value using two quotes " '...' "
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;
In psql there is a mechanism via the
\set name valcommand, 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.