Overview

Basic Features

• Connect to the database: For details, see "Using the gsql Client for Connection" in the Administrator Guide.

  NOTE: If the gsql client is used to connect to a database, the connection timeout period will be 5 minutes by default. 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 resolve this problem, see FAQs.

• Run SQL statements: Interactively entered SQL statements and specified SQL statements in a file can be run.

• 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.

Table 1 Advanced features of gsql

Feature Name	Description
Variables	gsql provides a variable feature that is similar to the shell command of Linux. The following \set meta-command of gsql can be used to set a variable: \set varname value To delete a variable, run the following command: \unset varname NOTE: - A variable is a simple name-value pair, where the value can be any characters in any length. - Variable names must consist of case-sensitive letters (including non-Latin letters), digits, and underscores(_). - If the \set varname meta-command (without the second parameter) is used, the variable is set without a value specified. - If the \set meta-command without parameters is used, values of all variables are displayed.
SQL substitution	Common SQL statements can be set to variables using the variable feature of gsql to simplify operations.
Customized prompt	Prompts of gsql can be customized. Prompts can be modified by changing the reserved variables of gsql: PROMPT1, PROMPT2, and PROMPT3. These variables can be set to customized values or the values predefined by gsql.
Automatic command completion	According to the MogDB syntax rules, gsql supports automatic command completion by pressing Tab. This function is enabled when the --with-readline option is specified during compilation and the -r parameter is specified during client connection. For example, if you enter crea and then press Tab, gsql will change it to create. NOTE: - Automatic completion of database SQL keywords such as SELECT, CREATE, and TABLE is supported. - Automatic completion of user-defined identifiers such as table names and view names is supported. - Automatic completion of meta-command options S and + is not supported. - Automatic completion of system catalogs prefixed with pg_ is supported. - Completion of column types is not supported during table creation. - No completion is supported after the SELECT operation. - Automatic completion of constants and macros is not supported. - The select * from a,b… statement does not support automatic completion from the second table. The insert into t1 (col1, col2, …) statement does not support automatic completion from the second column. - Automatic completion of parameters after with in the CREATE TABLESPACE statement is not supported. - The local and global indexes cannot be automatically completed during index creation, and the rebuild index cannot be automatically completed during index modification. - Automatic completion of parameters of the user and superuser levels in the SET statement is supported. - Automatic completion of IF EXISTS is not supported. - Automatic completion of Table name.Column name is not supported, for example, alter sequence <name> owned by tableName.colName and owned by. - Automatic completion of user-defined operators is not supported. If you copy and paste a command and press Tab, the command format may be incorrect. As a result, the command cannot be executed.
Historical client operation records	gsql can record historical client operations. This function is enabled by specifying the -r parameter when a client is connected. The number of historical records can be set using the \set command. For example, \set HISTSIZE 50 indicates that the number of historical records is set to 50. \set HISTSIZE 0 indicates that the operation history is not recorded. NOTE: - The default number of historical records is 32. The maximum number of historical records is 500. If interactively entered SQL commands contain Chinese characters, only the UTF-8 encoding environment is supported. - For security reasons, the records containing sensitive words, such as PASSWORD and IDENTIFIED, are regarded sensitive and not recorded in historical information. This indicates that you cannot view these records in command output histories.

• Environment variables

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

  mogdb=# \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:

  mogdb=# \echo :foo
  bar

  The variable reference 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.

  NOTE:

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

  Table 2 Settings of special variables

  Environment Variable	Setting Method	Description
  DBNAME	\set DBNAME dbname	Name of the connected database. This variable is set again when a database is connected.
  ECHO	`\set ECHO all	queries`
  ECHO_HIDDEN	`\set ECHO_HIDDEN on	off
  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: A proper variable value helps reduce the memory usage. The recommended value range is from 100 to 1000.
  HOST	\set HOST hostname	Specifies the name of a connected host.
  IGNOREEOF	\set IGNOREEOF variable	- If this variable is set to a number, for example, 10, the first nine EOF characters entered (generally by pressing Ctrl+D) in gsql are ignored and the gsql program exits when Ctrl+D is pressed tenth times. - 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	Specifies the 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
  ON_ERROR_STOP	`\set ON_ERROR_STOP on	off`
  PORT	\set PORT port	Specifies the port number of a connected database.
  USER	\set USER username	Specifies the database user you are currently connected as.
  VERBOSITY	`\set VERBOSITY terse	default

• 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 (:) before the variable. For example:

  mogdb=# \set foo 'HR.areaS'
  mogdb=# 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.

  NOTICE: The value of the variable is copied literally, so it can even contain unbalanced quotation marks or backslash commands. 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	Specifies the 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]: mogdb=> \set PROMPT1 %M [local:/tmp/gaussdba_mppdb] - Change the prompt to name: mogdb=> \set PROMPT1 name name - Change the prompt to =: mogdb=> \set PROMPT1 %R =
  PROMPT2	Specifies the prompt displayed when more input is expected because the command that is not terminated with a semicolon (;) or a quote (") is not closed.	PROMPT2 can be used to display the prompt. mogdb=# \set PROMPT2 TEST mogdb=# select * from HR.areaS TEST; `area_id
  PROMPT3	Specifies the 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. mogdb=# \set PROMPT3 '>>>>' mogdb=# 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	Specifies the full host name (with domain name). The full name is [local] if the connection is over a Unix domain socket, or [local:/dir/name] if the Unix domain socket is not at the compiled default location.
  %m	Specifies the host name truncated at the first dot. It is [local] if the connection is over a Unix domain socket.
  %>	Specifies the number of the port that the host is listening on.
  %n	Replaced with the database session username.
  %/	Replaced with the name of the current database.
  %~	Similar to %/. However, the output is tilde (~) if the database is your default database.
  %#	Uses # if the session user is the database administrator. Otherwise, uses >.
  %R	- In PROMPT1 normally =, but ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails). - In PROMPT2 %R is replaced with a hyphen (-), an asterisk (*), a single or double quotation mark, or a dollar sign ($), depending on whether gsql expects more input because the query is inside a /…/ comment or inside a quoted or dollar-escaped string.*
  %x	Replaced with the transaction status. - 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 indefinite (for example, because there is no connection).
  %digits	Replaced with the character with the specified byte.
  %:name	Specifies the value of the name variable of gsql.
  %command	Specifies command output, similar to substitution with the "^" symbol.
  %[ … %]	Prompts may contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. For example: mogdb=> \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, color-capable terminals.

  Environment Variables

  Table 5 Environment variables related to gsql

  Name	Description
  COLUMNS	If \set columns is set to 0, this parameter controls the width of the wrapped format. This width determines whether to change the wide output mode into the vertical output mode if automatic expansion is enabled.
  PAGER	If the query results do not fit on the screen, they are redirected through this command. You can use the \pset command to disable the pager. Typically, the more or less command is used for viewing the query result page by page. The default is platform-dependent. NOTE: Display of the less command is affected by the LC_CTYPE environment variable.
  PSQL_EDITOR	The \e and \ef commands use the editor specified by the environment variables. The variables are examined in the order listed. The default editor on Unix is vi.
  EDITOR
  VISUAL
  PSQL_EDITOR_LINENUMBER_ARG	When the \e or \ef command is used with a line number parameter, this variable specifies the command-line parameter used to pass the starting line number to the editor. For editors, such as Emacs or vi, this is a plus sign. Include a space in the value of the variable if space is needed between the option name and the line number. For example: PSQL_EDITOR_LINENUMBER_ARG = '+' PSQL_EDITOR_LINENUMBER_ARG='-line ' A plus sign (+) is used by default on Unix.
  PSQLRC	Specifies the location of the user's .gsqlrc file.
  SHELL	Has the same effect as the ! command.
  TMPDIR	Specifies the directory for storing temporary files. The default value is /tmp.