gsql Overview

Basic Functions

• Connect to the database: By default, only the local server can be connected. To connect to a remote database, you must configure the server. For details, see "Working with Databases > Connecting to a Database > Using gsql to Connect to a Database > Remotely Connecting to a Database" in the Developer Guide.
  

  If the gsql client is used to connect to a database, the connection timeout period will be 5 minutes. If the database has not correctly set up a connection and authenticated the identity of the client within this period, gsql will time out and exit.

  To solve this problem, see Troubleshooting.

• Run SQL statements: Interactively entered SQL statements and specified SQL statements in a file can be executed.
• Run meta-commands: Meta-commands help the administrator view database object information, query cache information, format SQL output, and connect to a new database. For details about meta-commands, see Meta-Command Reference.

Advanced Features

Table 1 lists the advanced features of gsql.

\set varname value

\unset varname

• Variable
  To configure a variable, run the \set meta-command of gsql. For example, to set variable foo to bar, run the following command:

  1	openGauss=# \set foo bar

  To reference the value of a variable, add a colon (:) before the variable. For example, to view the value of variable foo, run the following command:

  1 2	openGauss=# \echo :foo bar

  This variable quotation method is suitable for regular SQL statements and meta-commands.

  gsql pre-defines some special variables and plans the values of these variables. To ensure compatibility with later versions, do not use these variables for other purposes. For details about special variables, see Table 2.

  

  • All the special variables consist of upper-case letters, digits, and underscores (_).
  • To view the default value of a special variable, run \echo :varname, for example, \echo :DBNAME.

  Table 2 Settings of special variables

  Variable	How to Set	Description
  DBNAME	\set DBNAME dbname	Name of the connected database. This variable is reconfigured when a database is connected.
  ECHO	\set ECHO all | queries	If this variable is set to all, only the query information is displayed. This has the same effect as specifying the -a parameter when gsql is used to connect to a database. If this variable is set to queries, the command line and query information are displayed. This has the same effect as specifying the -e parameter when gsql is used to connect to a database.
  ECHO_HIDDEN	\set ECHO_HIDDEN on | off | noexec	When a meta-command (such as \dg) is used to query database information, the value of this variable determines the query behavior. If this variable is set to on, the query statements that are called by the meta-command are displayed, and then the query result is displayed. This has the same effect as specifying the -E parameter when gsql is used to connect to a database. If this variable is set to off, only the query result is displayed. If this variable is set to noexec, only the query information is displayed, and the query is not run.
  ENCODING	\set ENCODING encoding	Character set encoding of the current client.
  FETCH_COUNT	\set FETCH_COUNT variable	If the value is an integer greater than 0, for example, n, n lines will be selected from the result set to the cache and displayed on the screen when the SELECT statement is run. If this variable is not set or set to a value less than or equal to 0, all results are selected at a time to the cache when the SELECT statement is run. NOTE: Setting this variable to a proper value reduces memory usage. Generally, the proper value ranges from 100 to 1000.
  HISTCONTROL	\set HISTCONTROL ignorespace | ignoredups | ignoreboth | none	ignorespace: A line started with a space is not written to the historical record. ignoredups: A line that exists in the historical record is not written to the historical record. ignoreboth, none, or other values: All the lines read in interaction mode are saved in the historical record. NOTE: none indicates that HISTCONTROL is not configured.
  HISTFILE	\set HISTFILE filename	This file is used to store historical records. The default value is ~/.bash_history.
  HISTSIZE	\set HISTSIZE size	Number of commands in the history command. The default value is 500.
  HOST	\set HOST hostname	Name of the connected database host.
  IGNOREEOF	\set IGNOREEOF variable	If this variable is set to a number, for example, 10, the first nine EOF characters (generally Ctrl+C) entered in gsql are ignored and the gsql program exits when the tenth Ctrl+C is entered. If this variable is set to a non-numeric value, the default value is 10. If this variable is deleted, gsql exits when an EOF is entered.
  LASTOID	\set LASTOID oid	Last OID, which is the value returned by an INSERT or lo_import command. This variable is valid only before the output of the next SQL statement is displayed.
  ON_ERROR_ROLLBACK	\set ON_ERROR_ROLLBACK on | interactive | off	If the value is on, an error that may occur in a statement in a transaction block is ignored and the transaction continues. If the value is interactive, the error is ignored only in an interactive session. If the value is off (the default value), the error triggers the rollback of the transaction block. In on_error_rollback-on mode, a SAVEPOINT is configured before each statement of a transaction block, and an error triggers the rollback of the transaction block.
  ON_ERROR_STOP	\set ON_ERROR_STOP on | off	on: specifies that the execution stops if an error occurs. In interactive mode, gsql returns the output of executed commands immediately. off (default value): specifies that an error that occurs during the execution is ignored, and the execution continues.
  PORT	\set PORT port	Port number of a connected database.
  USER	\set USER username	Database user that is currently used for connection.
  VERBOSITY	\set VERBOSITY terse | default | verbose	This variable can be set to terse, default, or verbose to control redundant lines of error reports. terse: Only critical and major error texts and text locations are returned (which is suitable for single-line error information). default: Critical and major error texts and text locations, error details, and error messages (possibly involving multiple lines) are all returned. verbose: All error information is returned.

• SQL substitution
  gsql, like a parameter of a meta-command, provides a key feature that enables you to substitute a standard SQL statement for a gsql variable. gsql also provides a new alias or identifier for the variable. To replace the value of a variable using the SQL substitution method, add a colon (:) in front of the variable. For example:

  1 2 3 4 5 6 7 8 9

  openGauss=# \set foo 'HR.areaS' openGauss=# select * from :foo; area_id | area_name ---------+------------------------ 4 | Middle East and Africa 3 | Asia 1 | Europe 2 | Americas (4 rows)

  The above command queries the HR.areaS table.

  

  The value of a variable is copied character by character, and even an asymmetric quote mark or backslash (\) is copied. Therefore, the input content must be meaningful.

• Prompt

  The gsql prompt can be set using the three variables in Table 3. These variables consist of characters and special escape characters.

  Table 3 Prompt variables

  Variable	Description	Example
  PROMPT1	Normal prompt used when gsql requests a new command. The default value of PROMPT1 is: %/%R%#	PROMPT1 can be used to change the prompt. Change the prompt to [local]: 1 2 openGauss=> \set PROMPT1 %M [local:/tmp/gaussdba_mppdb] Change the prompt to name: 1 2 openGauss=> \set PROMPT1 name name Change the prompt to =: 1 2 openGauss=> \set PROMPT1 %R =
  PROMPT2	Prompt displayed when more command input is expected. For example, it is expected if a command is not terminated with a semicolon (;) or a quote (") is not closed.	PROMPT2 can be used to display the prompt. 1 2 3 4 5 6 7 8 9 openGauss=# \set PROMPT2 TEST openGauss=# select * from HR.areaS TEST; area_id | area_name ---------+-------------------- 1 | Europe 2 | Americas 4 | Middle East and Africa 3 | Asia (4 rows))
  PROMPT3	Prompt displayed when the COPY statement (such as COPY FROM STDIN) is run and data input is expected.	PROMPT3 can be used to display the COPY prompt. 1 2 3 4 5 6 7 openGauss=# \set PROMPT3 '>>>>' openGauss=# copy HR.areaS from STDIN; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >>>>1 aa >>>>2 bb >>>>\.

  The value of the selected prompt variable is printed literally. However, a value containing a percent sign (%) is replaced by the predefined contents depending on the character following the percent sign (%). For details about the defined substitutions, see Table 4.

  Table 4 Defined substitutions

  Symbol	Description
  %M	Full host name (with domain name). It can be [local] if the connection is over a Unix domain socket, or [local:/dir/name], if the Unix domain socket is not at the compiled in default location.
  %m	Host name truncated at the first dot. It is [local] if the connection is over a Unix domain socket.
  %>	Number of the port that the host is listening on.
  %n	User name of a database session.
  %/	Name of the current database.
  %~	Similar to %/. If the database is the default database, the output is a tilde (~).
  %#	If the session user is a database system administrator, use #. Otherwise, use >.
  %R	= is normally used for PROMPT1, but ^ is used in single-line mode and ! is used if the session is disconnected from the database (which may occur if \connect fails). For PROMPT2, the sequence is replaced by a hyphen (-), asterisk (*), single quotation mark ('), double quotation mark ("), or dollar sign ($), depending on whether gsql is waiting for more input, or the query is not terminated, or the query is in the /* ... */ the comment, quotation mark, or dollar sign extension.
  %x	Transaction state. An empty string when it is not in a transaction block An asterisk (*) when it is in a transaction block An exclamation mark (!) when it is in a failed transaction block A question mark (?) when the transaction status is indeterminate (for example, because there is no connection).
  %digits	Is replaced with the character with the specified byte.
  %:name	Value of the gsql variable name.
  %command	Command output, similar to ordinary "back-tick" ("^") substitution.
  %[ . . . %]	Prompts can contain terminal control characters which can change the color, background, or style of the prompt text, or change the title of the terminal window. For example: openGauss=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%#' The output is a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible and color-capable terminals.

Environment Variables

PSQL_EDITOR_LINENUMBER_ARG = '+'  
PSQL_EDITOR_LINENUMBER_ARG='--line '