<Oops, resent, wrong from again, I must really do something about my 1000 mail addresses>
Hello Tom,
I concur that this is expanding pgbench's expression language well beyond what anybody has shown a need for.
As for the motivation, I'm assuming that pgbench should provide features necessary to implement benchmarks, so I'm adding operators that appear in standard benchmark specifications.
From TPC-B 2.0.0 section 5.3.5:
| The Account_ID is generated as follows: | • A random number X is generated within [0,1] | • If X<0.85 or branches = 1, a random Account_ID is selected over all | <Branch_ID> accounts. | • If X>=0.85 and branches > 1, a random Account_ID is selected over all | non-<Branch_ID> accounts. The above uses a condition (If), logic (or, and), comparisons (=, <, >=).
From TPC-C 5.11 section 2.1.6, a bitwise-or operator is used to skew a
distribution: | NURand (A, x, y) = (((random (0, A) | random (x, y)) + C) % (y - x + 1)) + x And from section 5.2.5.4 of same, some time is computed based on a logarithm: | Tt = -log(r) * µ
I'm also concerned that there's an opportunity cost here, in that the patch establishes a precedence ordering for its new operators, which we'd have a hard time changing later. That's significant because the proposed precedence is different from what you'd get for similarly-named operators on the backend side. I realize that it's trying to follow the C standard instead,
Oops. I just looked at the precedence from a C parser, without realizing that precedence there was different from postgres SQL implementation:-( This is a bug on my part.
I'd be okay with the parts of this that duplicate existing backend syntax and semantics, but I don't especially want to invent further than that.
Okay. In the two latest versions sent, discrepancies from that were bugs, I was trying to conform.
Version 8 attached hopefully fixes the precedence issue raised above: - use precedence taken from "backend/gram.y" instead of C. I'm not sure that it is wise that pg has C-like operators with a different precedence, but this is probably much too late... And fixes the documentation: - remove a non existing anymore "if" function documentation which made Robert assume that I had not taken the hint to remove it. I had! - reorder operator documentation by their pg SQL precedence. -- Fabien.
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 1eee8dc..73101e1 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -828,11 +828,11 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
The expression may contain integer constants such as <literal>5432</>,
double constants such as <literal>3.14159</>,
references to variables <literal>:</><replaceable>variablename</>,
- unary operators (<literal>+</>, <literal>-</>) and binary operators
- (<literal>+</>, <literal>-</>, <literal>*</>, <literal>/</>,
- <literal>%</>) with their usual precedence and associativity,
- <link linkend="pgbench-builtin-functions">function calls</>, and
- parentheses.
+ <link linkend="pgbench-builtin-operators">operators</>
+ with their usual precedence and associativity,
+ <link linkend="pgbench-builtin-functions">function calls</>,
+ SQL <literal>CASE</> generic conditional expressions
+ and parentheses.
</para>
<para>
@@ -917,6 +917,165 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
</variablelist>
</refsect2>
+ <refsect2 id="pgbench-builtin-operators">
+ <title>Built-In Operators</title>
+
+ <para>
+ The arithmetic, bitwise, comparison and logical operators listed in
+ <xref linkend="pgbench-operators"> are built into <application>pgbench</>
+ and may be used in expressions appearing in
+ <link linkend="pgbench-metacommand-set"><literal>\set</literal></link>.
+ </para>
+
+ <table id="pgbench-operators">
+ <title>pgbench Operators by increasing precedence</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Operator</>
+ <entry>Description</>
+ <entry>Example</>
+ <entry>Result</>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>OR</></>
+ <entry>logical or</>
+ <entry><literal>5 or 0</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>AND</></>
+ <entry>logical and</>
+ <entry><literal>3 and 0</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal>NOT</></>
+ <entry>logical not</>
+ <entry><literal>not 0</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>=</></>
+ <entry>is equal</>
+ <entry><literal>5 = 4</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal><></></>
+ <entry>is not equal</>
+ <entry><literal>5 <> 4</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>!=</></>
+ <entry>is not equal</>
+ <entry><literal>5 != 5</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal><</></>
+ <entry>lower than</>
+ <entry><literal>5 < 4</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal><=</></>
+ <entry>lower or equal</>
+ <entry><literal>5 <= 4</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal>></></>
+ <entry>greater than</>
+ <entry><literal>5 > 4</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>>=</></>
+ <entry>greater or equal</>
+ <entry><literal>5 >= 4</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>|</></>
+ <entry>integer bitwise OR</>
+ <entry><literal>1 | 2</></>
+ <entry><literal>3</></>
+ </row>
+ <row>
+ <entry><literal>#</></>
+ <entry>integer bitwise XOR</>
+ <entry><literal>1 # 3</></>
+ <entry><literal>2</></>
+ </row>
+ <row>
+ <entry><literal>&</></>
+ <entry>integer bitwise AND</>
+ <entry><literal>1 & 3</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>~</></>
+ <entry>integer bitwise NOT</>
+ <entry><literal>~ 1</></>
+ <entry><literal>-2</></>
+ </row>
+ <row>
+ <entry><literal><<</></>
+ <entry>bitwise shift left</>
+ <entry><literal>1 << 2</></>
+ <entry><literal>4</></>
+ </row>
+ <row>
+ <entry><literal>>></></>
+ <entry>bitwise shift right</>
+ <entry><literal>8 >> 2</></>
+ <entry><literal>2</></>
+ </row>
+ <row>
+ <entry><literal>+</></>
+ <entry>addition</>
+ <entry><literal>5 + 4</></>
+ <entry><literal>9</></>
+ </row>
+ <row>
+ <entry><literal>-</></>
+ <entry>substraction</>
+ <entry><literal>3 - 2.0</></>
+ <entry><literal>1.0</></>
+ </row>
+ <row>
+ <entry><literal>*</></>
+ <entry>multiplication</>
+ <entry><literal>5 * 4</></>
+ <entry><literal>20</></>
+ </row>
+ <row>
+ <entry><literal>/</></>
+ <entry>division (integer truncates the results)</>
+ <entry><literal>5 / 3</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>%</></>
+ <entry>modulo</>
+ <entry><literal>3 % 2</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>-</></>
+ <entry>opposite</>
+ <entry><literal>- 2.0</></>
+ <entry><literal>-2.0</></>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </refsect2>
+
<refsect2 id="pgbench-builtin-functions">
<title>Built-In Functions</title>
@@ -963,6 +1122,13 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
<entry><literal>5432.0</></>
</row>
<row>
+ <entry><literal><function>exp(<replaceable>x</>)</></></>
+ <entry>double</>
+ <entry>exponential</>
+ <entry><literal>exp(1.0)</></>
+ <entry><literal>2.718281828459045</></>
+ </row>
+ <row>
<entry><literal><function>greatest(<replaceable>a</> [, <replaceable>...</> ] )</></></>
<entry>double if any <replaceable>a</> is double, else integer</>
<entry>largest value among arguments</>
@@ -984,6 +1150,13 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
<entry><literal>2.1</></>
</row>
<row>
+ <entry><literal><function>ln(<replaceable>x</>)</></></>
+ <entry>double</>
+ <entry>natural logarithm</>
+ <entry><literal>ln(2.718281828459045)</></>
+ <entry><literal>1.0</></>
+ </row>
+ <row>
<entry><literal><function>pi()</></></>
<entry>double</>
<entry>value of the constant PI</>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index b3a2d9b..0f0bdd6 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -24,8 +24,10 @@ static PgBenchExpr *make_double_constant(double dval);
static PgBenchExpr *make_variable(char *varname);
static PgBenchExpr *make_op(yyscan_t yyscanner, const char *operator,
PgBenchExpr *lexpr, PgBenchExpr *rexpr);
+static PgBenchExpr *make_uop(yyscan_t yyscanner, const char *operator, PgBenchExpr *expr);
static int find_func(yyscan_t yyscanner, const char *fname);
static PgBenchExpr *make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *args);
+static PgBenchExpr *make_case(yyscan_t yyscanner, PgBenchExprList *when_then_list, PgBenchExpr *else_part);
%}
@@ -45,18 +47,25 @@ static PgBenchExpr *make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *
PgBenchExprList *elist;
}
-%type <elist> elist
-%type <expr> expr
+%type <elist> elist when_then_list
+%type <expr> expr case_control
%type <ival> INTEGER_CONST function
%type <dval> DOUBLE_CONST
%type <str> VARIABLE FUNCTION
%token INTEGER_CONST DOUBLE_CONST VARIABLE FUNCTION
-
-/* Precedence: lowest to highest */
+%token AND_OP OR_OP NE_OP LE_OP GE_OP LS_OP RS_OP
+%token CASE_KW WHEN_KW THEN_KW ELSE_KW END_KW
+
+/* Precedence: lowest to highest, taken from postgres SQL parser */
+%left OR_OP
+%left AND_OP
+%right '!'
+%nonassoc '<' '>' '=' LE_OP GE_OP NE_OP
+%left '|' '#' '&' LS_OP RS_OP '~'
%left '+' '-'
%left '*' '/' '%'
-%right UMINUS
+%right UNARY
%%
@@ -68,20 +77,45 @@ elist: { $$ = NULL; }
;
expr: '(' expr ')' { $$ = $2; }
- | '+' expr %prec UMINUS { $$ = $2; }
- | '-' expr %prec UMINUS { $$ = make_op(yyscanner, "-",
+ | '+' expr %prec UNARY { $$ = $2; }
+ | '-' expr %prec UNARY { $$ = make_op(yyscanner, "-",
make_integer_constant(0), $2); }
+ | '~' expr { $$ = make_op(yyscanner, "#",
+ make_integer_constant(-1), $2); }
+ | '!' expr { $$ = make_uop(yyscanner, "!", $2); }
| expr '+' expr { $$ = make_op(yyscanner, "+", $1, $3); }
| expr '-' expr { $$ = make_op(yyscanner, "-", $1, $3); }
| expr '*' expr { $$ = make_op(yyscanner, "*", $1, $3); }
| expr '/' expr { $$ = make_op(yyscanner, "/", $1, $3); }
| expr '%' expr { $$ = make_op(yyscanner, "%", $1, $3); }
+ | expr '<' expr { $$ = make_op(yyscanner, "<", $1, $3); }
+ | expr LE_OP expr { $$ = make_op(yyscanner, "<=", $1, $3); }
+ | expr '>' expr { $$ = make_op(yyscanner, "<", $3, $1); }
+ | expr GE_OP expr { $$ = make_op(yyscanner, "<=", $3, $1); }
+ | expr '=' expr { $$ = make_op(yyscanner, "=", $1, $3); }
+ | expr NE_OP expr { $$ = make_op(yyscanner, "<>", $1, $3); }
+ | expr '&' expr { $$ = make_op(yyscanner, "&", $1, $3); }
+ | expr '|' expr { $$ = make_op(yyscanner, "|", $1, $3); }
+ | expr '#' expr { $$ = make_op(yyscanner, "#", $1, $3); }
+ | expr LS_OP expr { $$ = make_op(yyscanner, "<<", $1, $3); }
+ | expr RS_OP expr { $$ = make_op(yyscanner, ">>", $1, $3); }
+ | expr AND_OP expr { $$ = make_op(yyscanner, "&&", $1, $3); }
+ | expr OR_OP expr { $$ = make_op(yyscanner, "||", $1, $3); }
| INTEGER_CONST { $$ = make_integer_constant($1); }
| DOUBLE_CONST { $$ = make_double_constant($1); }
| VARIABLE { $$ = make_variable($1); }
| function '(' elist ')' { $$ = make_func(yyscanner, $1, $3); }
+ | case_control { $$ = $1; }
;
+when_then_list:
+ when_then_list WHEN_KW expr THEN_KW expr { $$ = make_elist($5, make_elist($3, $1)); }
+ | WHEN_KW expr THEN_KW expr { $$ = make_elist($4, make_elist($2, NULL)); }
+
+case_control:
+ CASE_KW when_then_list END_KW { $$ = make_case(yyscanner, $2, NULL); }
+ | CASE_KW when_then_list ELSE_KW expr END_KW { $$ = make_case(yyscanner, $2, $4); }
+
function: FUNCTION { $$ = find_func(yyscanner, $1); pg_free($1); }
;
@@ -119,6 +153,7 @@ make_variable(char *varname)
return expr;
}
+/* binary operators */
static PgBenchExpr *
make_op(yyscan_t yyscanner, const char *operator,
PgBenchExpr *lexpr, PgBenchExpr *rexpr)
@@ -127,6 +162,13 @@ make_op(yyscan_t yyscanner, const char *operator,
make_elist(rexpr, make_elist(lexpr, NULL)));
}
+/* unary operator */
+static PgBenchExpr *
+make_uop(yyscan_t yyscanner, const char *operator, PgBenchExpr *expr)
+{
+ return make_func(yyscanner, find_func(yyscanner, operator), make_elist(expr, NULL));
+}
+
/*
* List of available functions:
* - fname: function name
@@ -177,6 +219,12 @@ static const struct
"sqrt", 1, PGBENCH_SQRT
},
{
+ "ln", 1, PGBENCH_LN
+ },
+ {
+ "exp", 1, PGBENCH_EXP
+ },
+ {
"int", 1, PGBENCH_INT
},
{
@@ -191,6 +239,45 @@ static const struct
{
"random_exponential", 3, PGBENCH_RANDOM_EXPONENTIAL
},
+ {
+ "&&", 2, PGBENCH_AND
+ },
+ {
+ "||", 2, PGBENCH_OR
+ },
+ {
+ "!", 1, PGBENCH_NOT
+ },
+ {
+ "&", 2, PGBENCH_BITAND
+ },
+ {
+ "|", 2, PGBENCH_BITOR
+ },
+ {
+ "#", 2, PGBENCH_BITXOR
+ },
+ {
+ "<<", 2, PGBENCH_LSHIFT
+ },
+ {
+ ">>", 2, PGBENCH_RSHIFT
+ },
+ {
+ "=", 2, PGBENCH_EQ
+ },
+ {
+ "<>", 2, PGBENCH_NE
+ },
+ {
+ "<=", 2, PGBENCH_LE
+ },
+ {
+ "<", 2, PGBENCH_LT
+ },
+ {
+ "!case_end", -2, PGBENCH_CASE
+ },
/* keep as last array element */
{
NULL, 0, 0
@@ -279,6 +366,14 @@ make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *args)
elist_length(args) == 0)
expr_yyerror_more(yyscanner, "at least one argument expected",
PGBENCH_FUNCTIONS[fnumber].fname);
+ /* special case: case (when ... then ...)+ (else ...)? end */
+ if (PGBENCH_FUNCTIONS[fnumber].nargs == -2)
+ {
+ int len = elist_length(args);
+ if (len < 3 || len % 2 != 1)
+ expr_yyerror_more(yyscanner, "odd and >= 3 number of arguments expected",
+ "case control structure");
+ }
expr->etype = ENODE_FUNCTION;
expr->u.function.function = PGBENCH_FUNCTIONS[fnumber].tag;
@@ -291,6 +386,15 @@ make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *args)
return expr;
}
+static PgBenchExpr *
+make_case(yyscan_t yyscanner, PgBenchExprList *when_then_list, PgBenchExpr *else_part)
+{
+ if (else_part == NULL)
+ else_part = make_integer_constant(0);
+ when_then_list = make_elist(else_part, when_then_list);
+ return make_func(yyscanner, find_func(yyscanner, "!case_end"), when_then_list);
+}
+
/*
* exprscan.l is compiled as part of exprparse.y. Currently, this is
* unavoidable because exprparse does not create a .h file to export
diff --git a/src/bin/pgbench/exprscan.l b/src/bin/pgbench/exprscan.l
index dc1367b..3a701ae 100644
--- a/src/bin/pgbench/exprscan.l
+++ b/src/bin/pgbench/exprscan.l
@@ -69,6 +69,16 @@ newline [\n]
/* Line continuation marker */
continuation \\{newline}
+/* case insensitive keywords */
+and [Aa][Nn][Dd]
+or [Oo][Rr]
+not [Nn][Oo][Tt]
+case [Cc][Aa][Ss][Ee]
+when [Ww][Hh][Ee][Nn]
+then [Tt][Hh][Ee][Nn]
+else [Ee][Ll][Ss][Ee]
+end [Ee][Nn][Dd]
+
/* Exclusive states */
%x EXPR
@@ -127,11 +137,35 @@ continuation \\{newline}
"-" { return '-'; }
"*" { return '*'; }
"/" { return '/'; }
-"%" { return '%'; }
+"%" { return '%'; } /* C version, also in psql */
+"=" { return '='; }
+"<>" { return NE_OP; }
+"!=" { return NE_OP; } /* C version, also in psql */
+"<=" { return LE_OP; }
+">=" { return GE_OP; }
+"<<" { return LS_OP; }
+">>" { return RS_OP; }
+"<" { return '<'; }
+">" { return '>'; }
+"|" { return '|'; }
+"&" { return '&'; }
+"#" { return '#'; }
+"~" { return '~'; }
+
"(" { return '('; }
")" { return ')'; }
"," { return ','; }
+{and} { return AND_OP; }
+{or} { return OR_OP; }
+{not} { return '!'; }
+
+{case} { return CASE_KW; }
+{when} { return WHEN_KW; }
+{then} { return THEN_KW; }
+{else} { return ELSE_KW; }
+{end} { return END_KW; }
+
:{alnum}+ {
yylval->str = pg_strdup(yytext + 1);
return VARIABLE;
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index f6cb5d4..f72d05f 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -1266,6 +1266,19 @@ coerceToDouble(PgBenchValue *pval, double *dval)
}
}
+static bool coerceToBool(PgBenchValue *pval)
+{
+ if (pval->type == PGBT_DOUBLE)
+ {
+ return pval->u.dval != 0.0;
+ }
+ else
+ {
+ Assert(pval->type == PGBT_INT);
+ return pval->u.ival != 0;
+ }
+}
+
/* assign an integer value */
static void
setIntValue(PgBenchValue *pv, int64 ival)
@@ -1317,6 +1330,10 @@ evalFunc(TState *thread, CState *st,
case PGBENCH_MUL:
case PGBENCH_DIV:
case PGBENCH_MOD:
+ case PGBENCH_EQ:
+ case PGBENCH_NE:
+ case PGBENCH_LE:
+ case PGBENCH_LT:
{
PgBenchValue *lval = &vargs[0],
*rval = &vargs[1];
@@ -1352,6 +1369,22 @@ evalFunc(TState *thread, CState *st,
setDoubleValue(retval, ld / rd);
return true;
+ case PGBENCH_EQ:
+ setIntValue(retval, ld == rd);
+ return true;
+
+ case PGBENCH_NE:
+ setIntValue(retval, ld != rd);
+ return true;
+
+ case PGBENCH_LE:
+ setIntValue(retval, ld <= rd);
+ return true;
+
+ case PGBENCH_LT:
+ setIntValue(retval, ld < rd);
+ return true;
+
default:
/* cannot get here */
Assert(0);
@@ -1380,6 +1413,22 @@ evalFunc(TState *thread, CState *st,
setIntValue(retval, li * ri);
return true;
+ case PGBENCH_EQ:
+ setIntValue(retval, li == ri);
+ return true;
+
+ case PGBENCH_NE:
+ setIntValue(retval, li != ri);
+ return true;
+
+ case PGBENCH_LE:
+ setIntValue(retval, li <= ri);
+ return true;
+
+ case PGBENCH_LT:
+ setIntValue(retval, li < ri);
+ return true;
+
case PGBENCH_DIV:
case PGBENCH_MOD:
if (ri == 0)
@@ -1420,7 +1469,57 @@ evalFunc(TState *thread, CState *st,
}
}
- /* no arguments */
+ /* integer operators */
+ case PGBENCH_BITAND:
+ case PGBENCH_BITOR:
+ case PGBENCH_BITXOR:
+ case PGBENCH_LSHIFT:
+ case PGBENCH_RSHIFT:
+ {
+ int64 li, ri;
+
+ if (!coerceToInt(&vargs[0], &li) || !coerceToInt(&vargs[1], &ri))
+ return false;
+
+ if (func == PGBENCH_BITAND)
+ setIntValue(retval, li & ri);
+ else if (func == PGBENCH_BITOR)
+ setIntValue(retval, li | ri);
+ else if (func == PGBENCH_BITXOR)
+ setIntValue(retval, li ^ ri);
+ else if (func == PGBENCH_LSHIFT)
+ setIntValue(retval, li << ri);
+ else if (func == PGBENCH_RSHIFT)
+ setIntValue(retval, li >> ri);
+ else /* cannot get here */
+ Assert(0);
+
+ return true;
+ }
+
+ /* logical operators */
+ case PGBENCH_AND:
+ case PGBENCH_OR:
+ {
+ bool lb, rb;
+
+ lb = coerceToBool(&vargs[0]);
+ rb = coerceToBool(&vargs[1]);
+
+ if (func == PGBENCH_AND)
+ setIntValue(retval, lb && rb);
+ else if (func == PGBENCH_OR)
+ setIntValue(retval, lb || rb);
+ else /* cannot get here */
+ Assert(0);
+
+ return true;
+ }
+ case PGBENCH_NOT:
+ setIntValue(retval, !coerceToBool(&vargs[0]));
+ return true;
+
+ /* no arguments */
case PGBENCH_PI:
setDoubleValue(retval, M_PI);
return true;
@@ -1474,6 +1573,8 @@ evalFunc(TState *thread, CState *st,
/* 1 double argument */
case PGBENCH_DOUBLE:
case PGBENCH_SQRT:
+ case PGBENCH_LN:
+ case PGBENCH_EXP:
{
double dval;
@@ -1484,6 +1585,11 @@ evalFunc(TState *thread, CState *st,
if (func == PGBENCH_SQRT)
dval = sqrt(dval);
+ else if (func == PGBENCH_LN)
+ dval = log(dval);
+ else if (func == PGBENCH_EXP)
+ dval = exp(dval);
+ /* else is cast: do nothing */
setDoubleValue(retval, dval);
return true;
@@ -1635,6 +1741,24 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_CASE:
+ {
+ int n_when = nargs / 2, i;
+ Assert(nargs >= 3 && nargs % 2 == 1);
+ /* return on first true when condition */
+ for (i = 0; i < n_when; i++)
+ {
+ if (coerceToBool(&vargs[2*i]))
+ {
+ *retval = vargs[2*i+1];
+ return true;
+ }
+ }
+ /* else value is last */
+ *retval = vargs[nargs-1];
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index 38b3af5..dbf7ec9 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -73,9 +73,24 @@ typedef enum PgBenchFunction
PGBENCH_DOUBLE,
PGBENCH_PI,
PGBENCH_SQRT,
+ PGBENCH_LN,
+ PGBENCH_EXP,
PGBENCH_RANDOM,
PGBENCH_RANDOM_GAUSSIAN,
- PGBENCH_RANDOM_EXPONENTIAL
+ PGBENCH_RANDOM_EXPONENTIAL,
+ PGBENCH_AND,
+ PGBENCH_OR,
+ PGBENCH_NOT,
+ PGBENCH_BITAND,
+ PGBENCH_BITOR,
+ PGBENCH_BITXOR,
+ PGBENCH_LSHIFT,
+ PGBENCH_RSHIFT,
+ PGBENCH_EQ,
+ PGBENCH_NE,
+ PGBENCH_LE,
+ PGBENCH_LT,
+ PGBENCH_CASE
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/002_pgbench.pl b/src/bin/pgbench/t/002_pgbench.pl
new file mode 100644
index 0000000..3e58884
--- /dev/null
+++ b/src/bin/pgbench/t/002_pgbench.pl
@@ -0,0 +1,56 @@
+use strict;
+use warnings;
+
+use PostgresNode;
+use TestLib;
+use Test::More tests => 4;
+
+# Test pgbench custom expressions
+my $node = get_new_node('main');
+$node->init;
+$node->start;
+$node->safe_psql('postgres',
+ 'CREATE UNLOGGED TABLE pgbench_expr(id SERIAL PRIMARY KEY, val TEXT NOT NULL);');
+
+my $script = $node->basedir . '/pgbench_expressions';
+
+# test a number of expressions, which must produce increasing results
+my @value_exprs = (
+ # 1 .. 8: arithmetic and other operators
+ '1', '1+1', '1.5*2', '8/2', '15%10', '6.0E0', '(1<<3) - (-(-1))', '16 >> 1',
+ # 9 .. 17: various functions
+ 'abs(-9)', 'least(5432, 10, 111)', 'greatest(11.0, -1, 7, 9)', 'int(12.0)',
+ 'double(13)', 'int(pi() * 100)-300', 'sqrt(225)', 'ln(65536)/ln(2)',
+ 'int(exp(3.0) - 3)',
+ # 18 .. 19: case
+ 'CASE WHEN 1 AND 1 OR 0 THEN 18 END', 'case when not 0 then 19 else 0 end',
+ # 20: more case with comparisons
+ 'CASE WHEN 4 < 5 THEN 5 END * ' .
+ ' (CASE WHEN 3 <= 3 THEN 0.9 END + CASE WHEN 1 <> 0 THEN 0.8 ELSE 0 END + ' .
+ ' CASE WHEN 1 = 1 THEN 0.2 END + CASE WHEN 123 > 12 THEN 0.4 END + ' .
+ ' CASE WHEN 12 >= 12 THEN 0.6 END + CASE WHEN 17 < 18 THEN 0.1 END + 1)',
+ # 21 .. 24: bitwise operators
+ '16 | 4 | 1', '31 # 9', '55 & 151', '~(-25)'
+ # not tested: random functions
+);
+
+for my $expr (@value_exprs)
+{
+ append_to_file($script,
+ "\\set e $expr\nINSERT INTO pgbench_expr(val) VALUES (:e);\n");
+}
+
+$node->command_like(
+ [ qw(pgbench -n --transactions=1 --file), $script ],
+ qr{1/1},
+ 'pgbench expressions');
+
+my $stdout = $node->safe_psql(
+ 'postgres',
+ "SELECT COUNT(*) FILTER (WHERE id::FLOAT8 = val::FLOAT8) || '/' || COUNT(*)
+ FROM pgbench_expr;");
+
+#diag("stdout=$stdout");
+
+my $ntests = @value_exprs;
+like($stdout, qr{\b$ntests/$ntests\b}, 'pgbench expressions: results');
functions.sql
Description: application/sql
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
