Pass command line args to sql (Postgres)

Question

How can I pass command line args to sql files ran with psql (Postgres)?

i.e.

psql mydatabase < mysqlfile.sql arg1 arg2 arg3...

Is this possible?

Answer 1

Use variable interpolation feature in psql.

If you specify -v variable1=value1 or --set variable1=value1 parameter on command line, then :variable1 in the sql file will be replaced with corresponding text value.

Note: use standard-SQL quoted strings if you need quotes, spaces and so on.

Example:

echo "SELECT :arg1 FROM :arg2 LIMIT 10;" > script.sql
psql mydatabase -v arg1=relname -v arg2=pg_class < script.sql  
psql mydatabase -v arg1="'some string' as label" -v arg2=pg_namespace < script.sql