Let's consider the following example (from the start of a psql script):
\c :db_to_run_on
TRUNCATE the_most_important_table;
-- tried to avoid similarities to anything that exists out there
Now if it is run this by the command
psql [connection details] -v db_to_run_on=\'dev_database\'
then it just runs and the user is happy. But what if (s)he decides to specify -v db_to_run_on=production_database
? (Let's assume that this can happen, just like people run rm -rf / # don't try this at home!!!
ocassionally.) Hopefully there is a fresh backup of that table…
So the question arises: how to check the variables passed to a script and stop further processing based on their value?
Best Answer
There is an option in
psql
which stops executing commands on error, this isON_ERROR_STOP
. If we could raise an error somehow, this would do what we want.The problem is that we have to test the variable and produce an error somehow. Since one can't use control structures in
psql
(because there are none)*, my only idea was to use SQL for testing. Well, producing an error conditionally is something whichpl/pgsql
is quite good at, so I wrote a function which would generate an error. I can now call this function from a simpleCASE
structure. A simple example:*: You can use any shell commands after
\!
and conditionals of the shell, but since\!
opens a new shell, executing anything there does not have any effect for the current psql script.