With Postgres 10.10, here is what I am trying to accomplish. I want to have a test.sql script file (executed by \i /path/test.sql) whose code execution depends on what the user inputs after an initial prompt.
For instance, with code below I would like some text to be echoed and a table to be created, but the echoed text and the table name should depend on the text passed by the user as input to a prompt:
\prompt 'What is your favourite color?' answer
IF ('blue' = :answer) THEN
\echo 'It is blue! :) '
CREATE TABLE blue;
ELSE
\echo 'It is something else. :( '
CREATE TABLE :answer;
END IF;
However, a few errors prevent that from working:
myuser=# \i /home/myuser/test.sql
What is your favourite color?red
It is blue! :)
psql:/home/myuser/test.sql:4: ERROR: syntax error at or near "IF"
LINE 1: IF ('blue' = red) THEN
^
It is something else. :(
psql:/home/myuser/test.sql:7: ERROR: syntax error at or near "ELSE"
LINE 1: ELSE
^
psql:/home/myuser/test.sql:8: ERROR: syntax error at or near "IF"
LINE 1: END IF;
^
myuser=#
Notice that this is not the same as asked in https://dba.stackexchange.com/questions/207666/how-do-i-use-the-input-from-a-prompt-in-a-conditional-if. Also, I tried adapting it to follow the answer in Postgres syntax error at or near "IF", but then I just get more errors, starting with "syntax error at or near 'DO'".
Is there a way for me to accomplish this task? That is, every time an user executes the test.sql file with \i, the user is prompted and the execution of the code within the file branches depending on the text that the user inputed via prompt?
Something like this:
SELECT :'answer' = 'blue' AS isblue \gset
\if :isblue
...
\endif