Home • Explore • Blog • CI
☰



postgresql
17th chunk of `doc/src/sgml/ref/pgbench.sgml`
420a47367fe57c162bf5a99359790e16711a264eb746a1bd0000000100000fb2
 [<replaceable>prefix</replaceable>]</literal>
     <literal>\aset [<replaceable>prefix</replaceable>]</literal>
    </term>

    <listitem>
     <para>
      These commands may be used to end SQL queries, taking the place of the
      terminating semicolon (<literal>;</literal>).
     </para>

     <para>
      When the <literal>\gset</literal> 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 <replaceable>prefix</replaceable>
      if provided.
     </para>

     <para>
      When the <literal>\aset</literal> command is used, all combined SQL queries
      (separated by <literal>\;</literal>) have their columns stored into variables
      named after column names, and prefixed with <replaceable>prefix</replaceable>
      if provided. If a query returns no row, no assignment is made and the variable
      can be tested for existence to detect this. If a query returns more than one
      row, the last value is kept.
     </para>

     <para>
      <literal>\gset</literal> and <literal>\aset</literal> cannot be used in
      pipeline mode, since the query results are not yet available by the time
      the commands would need them.
     </para>

     <para>
      The following example puts the final account balance from the first query
      into variable <replaceable>abalance</replaceable>, and fills variables
      <replaceable>p_two</replaceable> and <replaceable>p_three</replaceable>
      with integers from the third query.
      The result of the second query is discarded.
      The result of the two last combined queries are stored in variables
      <replaceable>four</replaceable> and <replaceable>five</replaceable>.
<programlisting>
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_
SELECT 4 AS four \; SELECT 5 AS five \aset
</programlisting></para>
    </listitem>
   </varlistentry>

   <varlistentry id="pgbench-metacommand-if-else">
    <term><literal>\if</literal> <replaceable class="parameter">expression</replaceable></term>
    <term><literal>\elif</literal> <replaceable class="parameter">expression</replaceable></term>
    <term><literal>\else</literal></term>
    <term><literal>\endif</literal></term>
    <listitem>
     <para>
      This group of commands implements nestable conditional blocks,
      similarly to <literal>psql</literal>'s <xref linkend="psql-metacommand-if"/>.
      Conditional expressions are identical to those with <literal>\set</literal>,
      with non-zero values interpreted as true.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="pgbench-metacommand-set">
    <term>
     <literal>\set <replaceable>varname</replaceable> <replaceable>expression</replaceable></literal>
    </term>

    <listitem>
     <para>
      Sets variable <replaceable>varname</replaceable> to a value calculated
      from <replaceable>expression</replaceable>.
      The expression may contain the <literal>NULL</literal> constant,
      Boolean constants <literal>TRUE</literal> and <literal>FALSE</literal>,
      integer constants such as <literal>5432</literal>,
      double constants such as <literal>3.14159</literal>,
      references to variables <literal>:</literal><replaceable>variablename</replaceable>,
      <link linkend="pgbench-builtin-operators">operators</link>
      with their usual SQL precedence and associativity,
      <link linkend="pgbench-builtin-functions">function calls</link>,
      SQL <link linkend="functions-case"><token>CASE</token> generic conditional
      expressions</link> and parentheses.
     </para>

     <para>
      Functions and most operators return <literal>NULL</literal> on
      <literal>NULL</literal> input.
     </para>

     <para>
      For conditional purposes, non zero numerical values are
      <literal>TRUE</literal>,
Title: pgbench Meta Commands: \gset, \aset, \if, \elif, \else, \endif, and \set
Summary
This section describes several pgbench meta commands used within custom scripts. It starts with '\gset' and '\aset', used to end SQL queries and store the resulting column values into variables, optionally prefixed. The section then covers the conditional block commands: '\if', '\elif', '\else', and '\endif', which enable nestable conditional logic. Finally, it explains the '\set' command, which assigns a value to a variable calculated from an expression that can include constants, variable references, operators, functions, and SQL CASE expressions.