Here is a simple patch which adds a bunch of operators (bitwise: & | ^ ~, comparisons: =/== <>/!= < <= > >=, logical: and/&& or/|| xor/^^ not/!) and functions (exp ln if) to pgbench. I've tried to be pg's SQL compatible where appropriate.Also attached is a simple test script.
Here is a sightly updated version. -- Fabien.
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index f3afedb..ea319f6 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -821,9 +821,17 @@ 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,
+ arithmetic operators (unary: <literal>+</>, <literal>-</>; binary:
+ <literal>+</>, <literal>-</>, <literal>*</>, <literal>/</>, <literal>%</>),
+ bitwise operators (unary: <literal>~</>; binary: <literal>&</>,
+ <literal>|</>, <literal>#</>/<literal>^</>, <literal><<</>,
+ <literal>>></>)
+ comparisons (<literal>=</>/<literal>==</>, <literal><></>/<literal>!=</>,
+ <literal><=</>, <literal><</>, <literal>>=</>, <literal>></>),
+ logical operators (unary: <literal>not</>/<literal>!</>;
+ binary: <literal>and</>/<literal>&&</>,
+ <literal>or</>/<literal>||</>, <literal>xor</>/<literal>^^</>),
+ with their usual precedence and associativity,
<link linkend="pgbench-builtin-functions">function calls</>, and
parentheses.
</para>
@@ -955,6 +963,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</>
@@ -962,6 +977,13 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
<entry><literal>5</></>
</row>
<row>
+ <entry><literal><function>if(<replaceable>c</>,<replaceable>e1</>,<replaceable>e2</>)</></></>
+ <entry>same as <replaceable>e*</></>
+ <entry>if <replaceable>c</> is not zero then <replaceable>e1</> else <replaceable>e2</></>
+ <entry><literal>if(0,1.0,2.0)</></>
+ <entry><literal>2.0</></>
+ </row>
+ <row>
<entry><literal><function>int(<replaceable>x</>)</></></>
<entry>integer</>
<entry>cast to int</>
@@ -976,6 +998,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 0cc665b..233de9c 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -52,8 +52,22 @@ static PgBenchExpr *make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *
%type <str> VARIABLE FUNCTION
%token INTEGER_CONST DOUBLE_CONST VARIABLE FUNCTION
+%token AND_OP OR_OP XOR_OP NE_OP LE_OP GE_OP LS_OP RS_OP
/* Precedence: lowest to highest */
+
+/* logical */
+%left XOR_OP
+%left OR_OP
+%left AND_OP
+%right UNOT
+/* comparison */
+%left '=' NE_OP
+%left '<' '>' LE_OP GE_OP
+/* bitwise */
+%left '^' '|' '&' LS_OP RS_OP
+%right UINV
+/* arithmetic */
%left '+' '-'
%left '*' '/' '%'
%right UMINUS
@@ -71,11 +85,29 @@ expr: '(' expr ')' { $$ = $2; }
| '+' expr %prec UMINUS { $$ = $2; }
| '-' expr %prec UMINUS { $$ = make_op(yyscanner, "-",
make_integer_constant(0), $2); }
+ | '~' expr %prec UINV { $$ = make_op(yyscanner, "^",
+ make_integer_constant(-1), $2); }
+ | '!' expr %prec UNOT { $$ = make_op(yyscanner, "^^",
+ make_integer_constant(1), $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); }
+ | expr XOR_OP expr { $$ = make_op(yyscanner, "^^", $1, $3); }
| INTEGER_CONST { $$ = make_integer_constant($1); }
| DOUBLE_CONST { $$ = make_double_constant($1); }
| VARIABLE { $$ = make_variable($1); }
@@ -177,6 +209,12 @@ static const struct
"sqrt", 1, PGBENCH_SQRT
},
{
+ "ln", 1, PGBENCH_LN
+ },
+ {
+ "exp", 1, PGBENCH_EXP
+ },
+ {
"int", 1, PGBENCH_INT
},
{
@@ -191,6 +229,45 @@ static const struct
{
"random_exponential", 3, PGBENCH_RANDOM_EXPONENTIAL
},
+ {
+ "&&", 2, PGBENCH_AND
+ },
+ {
+ "||", 2, PGBENCH_OR
+ },
+ {
+ "^^", 2, PGBENCH_XOR
+ },
+ {
+ "&", 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
+ },
+ {
+ "if", 3, PGBENCH_IF
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/exprscan.l b/src/bin/pgbench/exprscan.l
index 20891a3..0aef58b 100644
--- a/src/bin/pgbench/exprscan.l
+++ b/src/bin/pgbench/exprscan.l
@@ -113,6 +113,29 @@ newline [\n]
"*" { return '*'; }
"/" { return '/'; }
"%" { return '%'; }
+"=" { return '='; }
+"==" { return '='; } /* C version */
+"<>" { return NE_OP; }
+"!=" { return NE_OP; } /* C version */
+"<=" { return LE_OP; }
+">=" { return GE_OP; }
+"<<" { return LS_OP; }
+">>" { return RS_OP; }
+"<" { return '<'; }
+">" { return '>'; }
+"|" { return '|'; }
+"&" { return '&'; }
+"#" { return '^'; }
+"^" { return '^'; } /* C version */
+"~" { return '~'; }
+"and" { return AND_OP; }
+"&&" { return AND_OP; } /* C version */
+"or" { return OR_OP; }
+"||" { return OR_OP; } /* C version */
+"xor" { return XOR_OP; } /* should exist */
+"^^" { return XOR_OP; } /* should exist */
+"not" { return '!'; }
+"!" { return '!'; } /* C version */
"(" { return '('; }
")" { return ')'; }
"," { return ','; }
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 87fb006..d742e2f 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -1189,6 +1189,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)
@@ -1240,6 +1253,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];
@@ -1275,6 +1292,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);
@@ -1303,6 +1336,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)
@@ -1343,7 +1392,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 there */
+ Assert(0);
+
+ return true;
+ }
+
+ /* logical operators */
+ case PGBENCH_AND:
+ case PGBENCH_OR:
+ case PGBENCH_XOR:
+ {
+ 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 if (func == PGBENCH_XOR)
+ setIntValue(retval, lb ^ rb);
+ else /* cannot get there */
+ Assert(0);
+
+ return true;
+ }
+
+ /* no arguments */
case PGBENCH_PI:
setDoubleValue(retval, M_PI);
return true;
@@ -1397,6 +1496,8 @@ evalFunc(TState *thread, CState *st,
/* 1 double argument */
case PGBENCH_DOUBLE:
case PGBENCH_SQRT:
+ case PGBENCH_LN:
+ case PGBENCH_EXP:
{
double dval;
@@ -1407,6 +1508,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;
@@ -1558,6 +1664,12 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_IF:
+ /* should it do a lazy evaluation of the branch? */
+ Assert(nargs == 3);
+ *retval = coerceToBool(&vargs[0]) ? vargs[1] : vargs[2];
+ return true;
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index ab0f822..5306c40 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_XOR,
+ PGBENCH_BITAND,
+ PGBENCH_BITOR,
+ PGBENCH_BITXOR,
+ PGBENCH_LSHIFT,
+ PGBENCH_RSHIFT,
+ PGBENCH_EQ,
+ PGBENCH_NE,
+ PGBENCH_LE,
+ PGBENCH_LT,
+ PGBENCH_IF
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
functions.sql
Description: application/sql
-- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
