From bc7fa0c15c590ddf4872e426abd76c2634f22aca Mon Sep 17 00:00:00 2001 From: Teodor Sigaev Date: Tue, 9 Jan 2018 18:02:04 +0300 Subject: Improve scripting language in pgbench Added: - variable now might contain integer, double, boolean and null values - functions ln, exp - logical AND/OR/NOT - bitwise AND/OR/NOT/XOR - bit right/left shift - comparison operators - IS [NOT] (NULL|TRUE|FALSE) - conditional choice (in form of when/case/then) New operations and functions allow to implement more complicated test scenario. Author: Fabien Coelho with minor editorization by me Reviewed-By: Pavel Stehule, Jeevan Ladhe, me Discussion: https://www.postgresql.org/message-id/flat/alpine.DEB.2.10.1604030742390.31618@sto --- doc/src/sgml/ref/pgbench.sgml | 223 ++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 217 insertions(+), 6 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml index 1519fe78ef9..3dd492cec11 100644 --- a/doc/src/sgml/ref/pgbench.sgml +++ b/doc/src/sgml/ref/pgbench.sgml @@ -904,14 +904,32 @@ pgbench options d Sets variable varname to a value calculated from expression. - The expression may contain integer constants such as 5432, + The expression may contain the NULL constant, + boolean constants TRUE and FALSE, + integer constants such as 5432, double constants such as 3.14159, references to variables :variablename, - unary operators (+, -) and binary operators - (+, -, *, /, - %) with their usual precedence and associativity, - function calls, and - parentheses. + operators + with their usual SQL precedence and associativity, + function calls, + SQL CASE generic conditional + expressions and parentheses. + + + + Functions and most operators return NULL on + NULL input. + + + + For conditional purposes, non zero numerical values are + TRUE, zero numerical values and NULL + are FALSE. + + + + When no final ELSE clause is provided to a + CASE, the default value is NULL. @@ -920,6 +938,7 @@ pgbench options d \set ntellers 10 * :scale \set aid (1021 * random(1, 100000 * :scale)) % \ (100000 * :scale) + 1 +\set divx CASE WHEN :x <> 0 THEN :y/:x ELSE NULL END @@ -996,6 +1015,177 @@ pgbench options d + + Built-In Operators + + + The arithmetic, bitwise, comparison and logical operators listed in + are built into pgbench + and may be used in expressions appearing in + \set. + + + + pgbench Operators by increasing precedence + + + + Operator + Description + Example + Result + + + + + OR + logical or + 5 or 0 + TRUE + + + AND + logical and + 3 and 0 + FALSE + + + NOT + logical not + not false + TRUE + + + IS [NOT] (NULL|TRUE|FALSE) + value tests + 1 is null + FALSE + + + ISNULL|NOTNULL + null tests + 1 notnull + TRUE + + + = + is equal + 5 = 4 + FALSE + + + <> + is not equal + 5 <> 4 + TRUE + + + != + is not equal + 5 != 5 + FALSE + + + < + lower than + 5 < 4 + FALSE + + + <= + lower or equal + 5 <= 4 + FALSE + + + > + greater than + 5 > 4 + TRUE + + + >= + greater or equal + 5 >= 4 + TRUE + + + | + integer bitwise OR + 1 | 2 + 3 + + + # + integer bitwise XOR + 1 # 3 + 2 + + + & + integer bitwise AND + 1 & 3 + 1 + + + ~ + integer bitwise NOT + ~ 1 + -2 + + + << + integer bitwise shift left + 1 << 2 + 4 + + + >> + integer bitwise shift right + 8 >> 2 + 2 + + + + + addition + 5 + 4 + 9 + + + - + substraction + 3 - 2.0 + 1.0 + + + * + multiplication + 5 * 4 + 20 + + + / + division (integer truncates the results) + 5 / 3 + 1 + + + % + modulo + 3 % 2 + 1 + + + - + opposite + - 2.0 + -2.0 + + + +
+ + Built-In Functions @@ -1041,6 +1231,13 @@ pgbench options d double(5432) 5432.0 + + exp(x) + double + exponential + exp(1.0) + 2.718281828459045 + greatest(a [, ... ] ) double if any a is double, else integer @@ -1062,6 +1259,20 @@ pgbench options d least(5, 4, 3, 2.1) 2.1 + + ln(x) + double + natural logarithm + ln(2.718281828459045) + 1.0 + + + mod(i, bj) + integer + modulo + mod(54, 32) + 22 + pi() double -- cgit v1.2.3