Overview
Basic Features
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 Database Usage > Connecting to a Database > Using gsql to Connect to a Database > Remotely Connecting to a Database in the Developer Guide.
NOTE: If gsql 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 resolve this problem, see Troubleshooting.
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
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 the variables set by the \set command, run the following command: \unset varname NOTE:
For details about variable examples and descriptions, see Variables. | |
Common SQL statements can be set to variables using the variable feature of gsql to simplify operations. For details about examples and descriptions about SQL substitution, see SQL substitution. | |
Prompts of gsql can be customized. Prompts can be modified by changing the reserved three variables of gsql: PROMPT1, PROMPT2, and PROMPT3. These variables can be set to customized values or the values predefined by gsql. For details, see Prompt. | |
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:
|
To set a variable, run the \set meta-command of gsql. For example, to set variable foo to bar, run the following command:
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:
openGauss=# \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
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:
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.
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.
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
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
Environment Variables
Table 5 Environment variables related to gsql