only set variable in psql-script if not specified on the command-line

Question

I want to give the option to specify some variables on the command-line for my psql-scripts with.

 psql -v myVar=myValue

However I found no way to give those variables default values within the sql-script itself. The syntax:

\set MyVar defaultValue

overwrites the value myValue specified on the psql command-line unconditionally.

Is there any way to check if a variable is set within psql?

Answer 1

Setting default values for variables is not supported in psql, but you can do it using a workaround.

This psql command:

\set myVar :myVar

does nothing if the variable was already set via psql (this means, myVar is set again to his value), otherwise the variable is set literally to the string :myVar.

Using this circumstance and the other psql command \gset, you can actually set a default value for myVar. Put this on the top of you sql script:

\set myVar :myVar
-- now myVar is set to the string ':myVar' if was not already set.
-- Checking it using a CASE statement:
SELECT CASE 
  WHEN :'myVar'= ':myVar'
  THEN 'default_value' 
  ELSE :'myVar' 
END AS "myVar"  \gset -- < \gset call at end of the query

It seems to work only with text variables, but you can cast to numeric if you need numeric variables:

SELECT CASE 
  WHEN :'myVar'= ':myVar'
  THEN '10' 
  ELSE :'myVar' 
END::numeric AS "myVar"  \gset

How does \gset work:

\gset allows you to set a variable from the result of a select query. The result variable is named like the column name, this is why you need the clause AS "myVar" at the end of the query (don't forget the double quotes if you want to use variables names with uppercase letters).

For example the command:

SELECT 'hello' AS var1 \gset

sets the variable var1 to hello, the same as

\set var1 hello

See here for more infos: http://www.postgresql.org/docs/9.4/static/app-psql.html

Answer 2

As of psql version 11, you can do this

\if :{?myVar}
\else
   \set myVar default_value
\endif
\echo myVar is :'myVar'

See the "Meta-Commands" documentation.