Skip to content

Commit d57c7a7

Browse files
adunstanadunstan
committed
Provide a test for variable existence in psql
"\if :{?variable_name}" will be translated to "\if TRUE" if the variable exists and "\if FALSE" otherwise. Thus it will be possible to execute code conditionally on the existence of the variable, regardless of its value. Fabien Coelho, with some review by Robins Tharakan and some light text editing by me. Discussion: https://postgr.es/m/alpine.DEB.2.20.1708260835520.3627@lancre
1 parent 7148050 commit d57c7a7

File tree

6 files changed

+115
-1
lines changed

6 files changed

+115
-1
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -783,6 +783,10 @@ testdb=>
783783
The forms <literal>:'<replaceable>variable_name</>'</literal> and
784784
<literal>:"<replaceable>variable_name</>"</literal> described there
785785
work as well.
786+
The <literal>:{?<replaceable>variable_name</>}</> syntax allows
787+
testing whether a variable is defined. It is substituted by
788+
TRUE or FALSE.
789+
Escaping the colon with a backslash protects it from substitution.
786790
</para>
787791

788792
<para>
@@ -3938,6 +3942,12 @@ testdb=&gt; <userinput>INSERT INTO my_table VALUES (:'content');</userinput>
39383942
can escape a colon with a backslash to protect it from substitution.
39393943
</para>
39403944

3945+
<para>
3946+
The <literal>:{?<replaceable>name</>}</> special syntax returns TRUE
3947+
or FALSE depending on whether the variable exists or not, and is thus
3948+
always substituted, unless the colon is backslash-escaped.
3949+
</para>
3950+
39413951
<para>
39423952
The colon syntax for variables is standard <acronym>SQL</acronym> for
39433953
embedded query languages, such as <application>ECPG</application>.

‎src/bin/psql/psqlscanslash.l

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -281,6 +281,10 @@ other .
281281
unquoted_option_chars = 0;
282282
}
283283

284+
:\{\?{variable_char}+\} {
285+
psqlscan_test_variable(cur_state, yytext, yyleng);
286+
}
287+
284288
:'{variable_char}* {
285289
/* Throw back everything but the colon */
286290
yyless(1);
@@ -295,6 +299,20 @@ other .
295299
ECHO;
296300
}
297301

302+
:\{\?{variable_char}* {
303+
/* Throw back everything but the colon */
304+
yyless(1);
305+
unquoted_option_chars++;
306+
ECHO;
307+
}
308+
309+
:\{ {
310+
/* Throw back everything but the colon */
311+
yyless(1);
312+
unquoted_option_chars++;
313+
ECHO;
314+
}
315+
298316
{other} {
299317
unquoted_option_chars++;
300318
ECHO;

‎src/fe_utils/psqlscan.l

Lines changed: 41 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -745,9 +745,13 @@ other .
745745
PQUOTE_SQL_IDENT);
746746
}
747747

748+
:\{\?{variable_char}+\} {
749+
psqlscan_test_variable(cur_state, yytext, yyleng);
750+
}
751+
748752
/*
749753
* These rules just avoid the need for scanner backup if one of the
750-
* two rules above fails to match completely.
754+
* three rules above fails to match completely.
751755
*/
752756

753757
:'{variable_char}* {
@@ -762,6 +766,17 @@ other .
762766
ECHO;
763767
}
764768

769+
:\{\?{variable_char}* {
770+
/* Throw back everything but the colon */
771+
yyless(1);
772+
ECHO;
773+
}
774+
:\{ {
775+
/* Throw back everything but the colon */
776+
yyless(1);
777+
ECHO;
778+
}
779+
765780
/*
766781
* Back to backend-compatible rules.
767782
*/
@@ -1442,3 +1457,28 @@ psqlscan_escape_variable(PsqlScanState state, const char *txt, int len,
14421457
psqlscan_emit(state, txt, len);
14431458
}
14441459
}
1460+
1461+
void
1462+
psqlscan_test_variable(PsqlScanState state, const char *txt, int len)
1463+
{
1464+
char *varname;
1465+
char *value;
1466+
1467+
varname = psqlscan_extract_substring(state, txt + 3, len - 4);
1468+
if (state->callbacks->get_variable)
1469+
value = state->callbacks->get_variable(varname, PQUOTE_PLAIN,
1470+
state->cb_passthrough);
1471+
else
1472+
value = NULL;
1473+
free(varname);
1474+
1475+
if (value != NULL)
1476+
{
1477+
psqlscan_emit(state, "TRUE", 4);
1478+
free(value);
1479+
}
1480+
else
1481+
{
1482+
psqlscan_emit(state, "FALSE", 5);
1483+
}
1484+
}

‎src/include/fe_utils/psqlscan_int.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -142,5 +142,7 @@ extern char *psqlscan_extract_substring(PsqlScanState state,
142142
extern void psqlscan_escape_variable(PsqlScanState state,
143143
const char *txt, int len,
144144
PsqlScanQuoteType quote);
145+
extern void psqlscan_test_variable(PsqlScanState state,
146+
const char *txt, int len);
145147

146148
#endif /* PSQLSCAN_INT_H */

‎src/test/regress/expected/psql.out

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3014,6 +3014,32 @@ bar 'bar' "bar"
30143014
\echo 'should print #8-1'
30153015
should print #8-1
30163016
\endif
3017+
-- :{?...} defined variable test
3018+
\set i 1
3019+
\if :{?i}
3020+
\echo '#9-1 ok, variable i is defined'
3021+
#9-1 ok, variable i is defined
3022+
\else
3023+
\echo 'should not print #9-2'
3024+
\endif
3025+
\if :{?no_such_variable}
3026+
\echo 'should not print #10-1'
3027+
\else
3028+
\echo '#10-2 ok, variable no_such_variable is not defined'
3029+
#10-2 ok, variable no_such_variable is not defined
3030+
\endif
3031+
SELECT :{?i} AS i_is_defined;
3032+
i_is_defined
3033+
--------------
3034+
t
3035+
(1 row)
3036+
3037+
SELECT NOT :{?no_such_var} AS no_such_var_is_not_defined;
3038+
no_such_var_is_not_defined
3039+
----------------------------
3040+
t
3041+
(1 row)
3042+
30173043
-- SHOW_CONTEXT
30183044
\set SHOW_CONTEXT never
30193045
do $$

‎src/test/regress/sql/psql.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -572,6 +572,24 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
572572
\echo 'should print #8-1'
573573
\endif
574574

575+
-- :{?...} defined variable test
576+
\set i 1
577+
\if :{?i}
578+
\echo '#9-1 ok, variable i is defined'
579+
\else
580+
\echo 'should not print #9-2'
581+
\endif
582+
583+
\if :{?no_such_variable}
584+
\echo 'should not print #10-1'
585+
\else
586+
\echo '#10-2 ok, variable no_such_variable is not defined'
587+
\endif
588+
589+
SELECT :{?i} AS i_is_defined;
590+
591+
SELECT NOT :{?no_such_var} AS no_such_var_is_not_defined;
592+
575593
-- SHOW_CONTEXT
576594

577595
\set SHOW_CONTEXT never

0 commit comments

Comments
 (0)