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?
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
\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
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.
The answer from Tom-db is great but doesn't work when used with /prompt
To use this in the scenario where a user presses enter without adding anything in response to the /prompt don't use the \set myVar :myVar part and just use:
\prompt "Please provide value " myVar
/*user hits enter */
SELECT CASE
WHEN :'myVar'= ''
THEN 'default_value'
ELSE :'myVar'
END AS "myVar" \gset
select :'myVar';
