On 30th of March 2017, Tom Lane committed patch:
Support \if ... \elif ... \else ... \endif in psql scripting. This patch adds nestable conditional blocks to psql. The control structure feature per se is complete, but the boolean expressions understood by \if and \elif are pretty primitive; basically, after variable substitution and backtick expansion, the result has to be "true" or "false" or one of the other standard spellings of a boolean value. But that's enough for many purposes, since you can always do the heavy lifting on the server side; and we can extend it later. Along the way, pay down some of the technical debt that had built up around psql/command.c: * Refactor exec_command() into a function per command, instead of being a 1500-line monstrosity. This makes the file noticeably longer because of repetitive function header/trailer overhead, but it seems much more readable. * Teach psql_get_variable() and psqlscanslash.l to suppress variable substitution and backtick expansion on the basis of the conditional stack state, thereby allowing removal of the OT_NO_EVAL kluge. * Fix the no-doubt-once-expedient hack of sometimes silently substituting mainloop.c's previous_buf for query_buf when calling HandleSlashCmds. (It's a bit remarkable that commands like \r worked at all with that.) Recall of a previous query is now done explicitly in the slash commands where that should happen. Corey Huinker, reviewed by Fabien Coelho, further hacking by me Discussion: https://postgr.es/m/CADkLM=c94OSRTnat=LX0ivNq4pxDNeoomFfYvBKM5N_xfmLtAA@mail.gmail.com
This is great. I've long wanted more power in psql – as I use it all the time for all interactions with databases.
Now, we can have some logic in the scripts, which will definitely make certain things easier.
For example, let's consider this simplistic example:
SELECT current_setting('port') = '5100' AS is_master \gset \IF :is_master \SET PROMPT1 '\nmaster %x$ ' \ELSE \SET PROMPT1 '\nslave %x$ ' \endif
First line select true/false value depending on current port of server that I'm connected to. Then, \if will set appropriate prompt. Like this:
=$ PSQLRC=$( pwd )/test.sql psql -p 5100 psql (10devel) TYPE "help" FOR help. master $ \q =$ PSQLRC=$( pwd )/test.sql psql -p 5101 psql (10devel) TYPE "help" FOR help. slave $ \q
Of course inside \if blocks you can put anything you want – queries, inclusion of other files, or nested \if clauses. Great stuff, thanks a lot.