From 6260cc550b0e60052168518a0338e440b67cf24e Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 10 Jan 2019 13:42:20 -0300 Subject: pgbench: add \cset and \gset commands MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit These commands allow assignment of values produced by queries to pgbench variables, where they can be used by further commands. \gset terminates a command sequence (just like a bare semicolon); \cset separates multiple queries in a compound command, like an escaped semicolon (\;). A prefix can be provided to the \-command and is prepended to the name of each output column to produce the final variable name. This feature allows pgbench scripts to react meaningfully to the actual database contents, allowing more powerful benchmarks to be written. Authors: Fabien Coelho, Álvaro Herrera Reviewed-by: Amit Langote Reviewed-by: Stephen Frost Reviewed-by: Pavel Stehule Reviewed-by: Tom Lane Reviewed-by: Tatsuo Ishii Reviewed-by: Rafia Sabih Discussion: https://postgr.es/m/alpine.DEB.2.20.1607091005330.3412@sto --- doc/src/sgml/ref/pgbench.sgml | 85 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 85 insertions(+) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml index b5e3a62a339..ca160881188 100644 --- a/doc/src/sgml/ref/pgbench.sgml +++ b/doc/src/sgml/ref/pgbench.sgml @@ -954,6 +954,91 @@ pgbench options d + + + \cset [prefix] + + + + + This command may be used to end SQL queries, replacing an embedded + semicolon (\;) within a compound SQL command. + + + + When this command is used, the preceding SQL query is expected to + return one row, the columns of which are stored into variables named after + column names, and prefixed with prefix if provided. + + + + The following example sends four queries as one compound SQL command, + inducing one message sent at the protocol level. + The result of the first query is stored into variable one, + the results of the third query are stored into variables z_three + and z_four, + whereas the results of the other queries are discarded. + +-- compound of four queries +SELECT 1 AS one \cset +SELECT 2 AS two \; +SELECT 3 AS three, 4 AS four \cset z_ +SELECT 5; + + + + + + \cset does not work when empty SQL queries appear + within a compound SQL command. + + + + + + + + \gset [prefix] + + + + + This command may be used to end SQL queries, replacing a final semicolon + (;). + + + + When this command is used, the preceding SQL query is expected to + return one row, the columns of which are stored into variables named after + column names, and prefixed with prefix if provided. + + + + The following example puts the final account balance from the first query + into variable abalance, and fills variables + p_two and p_three + with integers from the last query. + The result of the second query is discarded. + +UPDATE pgbench_accounts + SET abalance = abalance + :delta + WHERE aid = :aid + RETURNING abalance \gset +-- compound of two queries +SELECT 1 \; +SELECT 2 AS two, 3 AS three \gset p_ + + + + + + \gset does not work when empty SQL queries appear + within a compound SQL command. + + + + + \if expression \elif expression -- cgit v1.2.3