Meta-Command Reference¶

This section describes meta-commands provided by gsql after the GaussDB(DWS) database CLI tool is used to connect to a database. A gsql meta-command can be anything that you enter in gsql and begins with an unquoted backslash.

Precautions¶

The format of the gsql meta-command is a backslash (\) followed by a command verb, and then a parameter. The parameters are separated from the command verb and from each other by any number of whitespace characters.
To include whitespace in a parameter, you can quote it with single quotation marks (''). To include single quotation marks in a parameter, add a backslash in front of it. Anything contained in single quotation marks is furthermore subject to C-like substitutions for \n (new line), \t (tab), \b (backspace), \r (carriage return), \f (form feed), \digits (octal), and \xdigits (hexadecimal).
Within a parameter, text enclosed in double quotation marks ("") is taken as a command line input to the shell. The command output (with any trailing newline removed) is taken as a parameter.
If an unquoted argument begins with a colon (:), the parameter is taken as a gsql variable and the value of the variable is used as the parameter value instead.
Some commands take an SQL identifier (such as a table name) as a parameter. These parameters follow the SQL syntax rules: Unquoted letters are forced to lowercase, while double quotation marks ("") protect letters from case conversion and allow incorporation of whitespace into the identifier. Within double quotation marks, paired double quotation marks reduce to a single double quotation mark in the result name. For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and "Aweird""name" becomes A weird"name.
Parameter parsing stops when another unquoted backslash appears. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of parameters and continues parsing SQL statements if any. In this way, SQL statements and gsql commands can be freely mixed in a row. However, the parameters of a meta-command cannot continue beyond the end of a line in any situations.

Meta-command¶

For details about meta-commands, see Table 1, Table 2, Table 3, Table 4, Table 6, Table 8, Table 9, Table 10, and Table 12.

Important

FILE mentioned in the following commands indicates a file path. This path can be an absolute path such as /home/gauss/file.txt or a relative path, such as file.txt. By default, a file.txt is created in the path where the user runs gsql commands.

**Table 1** Common meta-commands¶
Parameter	Description	Value Range
\copyright	Displays GaussDB(DWS) version and copyright information.	`-`
\g [FILE] or ;	Performs a query operation and sends the result to a file or pipe.	`-`
\h(\help) [NAME]	Provides syntax help on the specified SQL statement.	If the name is not specified, then gsql will list all the commands for which syntax help is available. If the name is an asterisk (`*`), syntax help on all SQL statements is displayed.
\parallel [on [num]\|off]	Controls the parallel execution function. on: The switch is enabled and the maximum number of concurrently executed tasks is num. off: This switch is disabled. Note Parallel execution is not allowed in a running transaction and a transaction is not allowed to be stared during parallel execution. Parallel execution of \d meta-commands is not allowed. If SELECT statements are run concurrently, customers can accept the problem that the return results are displayed randomly but they cannot accept it if a core dump or process response failure occurs. SET statements are not allowed in concurrent tasks because they may cause unexpected results. Temporary tables cannot be created. If temporary tables are required, create them before parallel execution is enabled, and use them only in the parallel execution. Temporary tables cannot be created in parallel execution. When \parallel is executed, num independent gsql processes can be connected to the database server. The duration of all jobs in \parallel cannot exceed the value of session_timeout. Otherwise, the connection may be interrupted during concurrent execution.	The default value of num is 1024. Important NOTICE: The maximum number of connections allowed by the server is determined based on max_connection and the number of current connections. Set the value of num based on the allowed number of connections.
\q [value]	Exits the gsql program. In a script file, this command is run only when a script terminates. The exit code is determined by the value.	`-`

**Table 2** Buffer query meta-commands¶
Parameter	Description
\e [FILE] [LINE]	Use an external editor to edit the query buffer or file.
\ef [FUNCNAME [LINE]]	Use an external editor to edit the function definition. If LINE is specified, the cursor will point to the specified line of the function body.
\p	Prints the current query buffer to the standard output.
`\r`	Resets (clears) the query buffer.
\w FILE	Outputs the current query buffer to a file.

**Table 3** Input and output meta-commands¶
Parameter	Description
\copy { table [ ( column_list ) ] \| ( query ) } { from \| to } { filename \| stdin \| stdout \| pstdin \| pstdout } [ with ] [ binary ] [ oids ] [ delimiter [ as ] 'character' ] [ null [ as ] 'string' ] [ csv [ header ] [ quote [ as ] 'character' ] [ escape [ as ] 'character' ] [ force quote column_list \| * ] [ force not null column_list ] ]	After logging in to the database on any gsql client, you can import and export data. This is an operation of running the SQL COPY command, but not the server that reads or writes data to a specified file. Instead, data is transferred between the server and the local file system. This means that the accessibility and permissions of the file are the permissions of the local user rather than the server. The initial database user permission is not required. Note \copy only applies to small-batch data import with uniform formats but poor error tolerance capability. GDS or COPY is preferred for data import.
\echo [STRING]	Writes a character string to the standard output.
\i FILE	Reads content from FILE and uses them as the input for a query.
\i+ FILE KEY	Runs commands in an encrypted file.
\ir FILE	Is similar to \i, but resolves relative path names differently.
\ir+ FILE KEY	Is similar to \i, but resolves relative path names differently.
\o [FILE]	Saves all query results to a file.
\qecho [STRING]	Prints a character string to the query result output.

Note

In Table 4, S indicates that the system object is displayed, and + indicates that additional object descriptions are displayed. PATTERN specifies the name of an object to be displayed.

**Table 4** Information display meta-commands¶
Parameter	Description	Value Range	Example
\d[S+]	Lists all tables, views, and sequences of all schemas in the search_path. When objects with the same name exist in different schemas in the search_path, only the object in the schema that ranks first in the search_path is displayed.	`-`	Lists all tables, views, and sequences of all schemas in the search_path. \d
\d[S+] NAME	Lists the structure of specified tables, views, and indexes.	`-`	Lists the structure of table a. \dtable+ a
\d+ [PATTERN]	Lists all tables, views, and indexes.	If PATTERN is specified, only tables, views, and indexes whose names match PATTERN are displayed.	Lists all tables, views, and indexes whose names start with f. \d+ f*
\da[S] [PATTERN]	Lists all available aggregate functions, together with their return value types and the data types.	If PATTERN is specified, only aggregate functions whose names match PATTERN are displayed.	Lists all available aggregate functions whose names start with f, together with their return value types and the data types. \da f*
\db[+] [PATTERN]	Lists all available tablespaces.	If PATTERN is specified, only tablespaces whose names match PATTERN are displayed.	Lists all available tablespaces whose names start with p. \db p*
\dc[S+] [PATTERN]	Lists all available conversions between character sets.	If PATTERN is specified, only conversions whose names match PATTERN are displayed.	Lists all available conversions between character sets. \dc *
\dC[+] [PATTERN]	Lists all type conversions.	If PATTERN is specified, only conversions whose names match PATTERN are displayed.	Lists all type conversion whose patten names start with c. \dC c*
\dd[S] [PATTERN]	Lists descriptions about objects matching PATTERN.	If PATTERN is not specified, all visible objects are displayed. The objects include aggregations, functions, operators, types, relations (table, view, index, sequence, and large object), and rules.	Lists all visible objects. \dd
\ddp [PATTERN]	Lists all default permissions.	If PATTERN is specified, only permissions whose names match PATTERN are displayed.	Lists all default permissions. \ddp
\dD[S+] [PATTERN]	Lists all available domains.	If PATTERN is specified, only domains whose names match PATTERN are displayed.	Lists all available domains. \dD
\ded[+] [PATTERN]	Lists all Data Source objects.	If PATTERN is specified, only objects whose names match PATTERN are displayed.	Lists all Data Source objects. \ded
\det[+] [PATTERN]	Lists all external tables.	If PATTERN is specified, only tables whose names match PATTERN are displayed.	Lists all external tables. \det
\des[+] [PATTERN]	Lists all external servers.	If PATTERN is specified, only servers whose names match PATTERN are displayed.	Lists all external servers. \des
\deu[+] [PATTERN]	Lists user mappings.	If PATTERN is specified, only information whose name matches PATTERN is displayed.	Lists user mappings. \deu
\dew[+] [PATTERN]	Lists foreign-data wrappers.	If PATTERN is specified, only data whose name matches PATTERN is displayed.	Lists foreign-data wrappers. \dew
\df[antw][S+] [PATTERN]	Lists all available functions, together with their parameters and return types. a indicates an aggregate function, n indicates a common function, t indicates a trigger, and w indicates a window function.	If PATTERN is specified, only functions whose names match PATTERN are displayed.	Lists all available functions, together with their parameters and return types. \df
\dF[+] [PATTERN]	Lists all text search configurations.	If PATTERN is specified, only configurations whose names match PATTERN are displayed.	Lists all text search configurations. \dF+
\dFd[+] [PATTERN]	Lists all text search dictionaries.	If PATTERN is specified, only dictionaries whose names match PATTERN are displayed.	Lists all text search dictionaries. \dFd
\dFp[+] [PATTERN]	Lists all text search parsers.	If PATTERN is specified, only analyzers whose names match PATTERN are displayed.	Lists all text search parsers. \dFp
\dFt[+] [PATTERN]	Lists all text search templates.	If PATTERN is specified, only templates whose names match PATTERN are displayed.	Lists all text search templates. \dFt
\dg[+] [PATTERN]	Lists all database roles. Note Since the concepts of "users" and "groups" have been unified into "roles", this command is now equivalent to \du. The two commands are all reserved for forward compatibility.	If PATTERN is specified, only roles whose names match PATTERN are displayed.	List all database roles whose names start with j and end with e. \dg j?e
\dl	This is an alias for \lo_list, which shows a list of large objects.	`-`	Lists all large objects. \dl
\dL[S+] [PATTERN]	Lists available procedural languages.	If PATTERN is specified, only languages whose names match PATTERN are displayed.	Lists available procedural languages. \dL
\dn[S+] [PATTERN]	Lists all schemas (namespace).	If PATTERN is specified, only schemas whose names match PATTERN are displayed. By default, only schemas you created are displayed.	Lists information about all schemas whose names start with d. \dn+ d*
\do[S] [PATTERN]	Lists available operators with their operand and return types.	If PATTERN is specified, only operators whose names match PATTERN are displayed. By default, only operators you created are displayed.	Lists available operators with their operand and return types. \do
\dO[S+] [PATTERN]	Lists collations.	If PATTERN is specified, only collations whose names match PATTERN are displayed. By default, only collations you created are displayed.	Lists collations. \dO
\dp [PATTERN]	Lists tables, views, and related permissions. The following result about \dp is displayed: rolename=xxxx/yyyy --Assigning permissions to a role =xxxx/yyyy --Assigning permissions to public xxxx indicates the assigned permissions, and yyyy indicates the roles that are assigned to the permissions. For details about permission descriptions, see Table 5.	If PATTERN is specified, only tables and views whose names match PATTERN are displayed.	Lists tables, views, and related permissions. \dp
\drds [PATTERN1 [PATTERN2]]	Lists all modified configuration parameters. These settings can be for roles, for databases, or for both. PATTERN1 and PATTERN2 indicate a role pattern and a database pattern, respectively.	If PATTERN is specified, only collations whose names match PATTERN are displayed. If the default value is used or * is specified, all settings are listed.	Lists all modified configuration parameters of the database. \drds *
\dT[S+] [PATTERN]	Lists all data types.	If PATTERN is specified, only types whose names match PATTERN are displayed.	Lists all data types. \dT
\du[+] [PATTERN]	Lists all database roles. Note Since the concepts of "users" and "groups" have been unified into "roles", this command is now equivalent to \dg. The two commands are all reserved for forward compatibility.	If PATTERN is specified, only roles whose names match PATTERN are displayed.	Lists all database roles. \du
\dE[S+] [PATTERN] \di[S+] [PATTERN] \ds[S+] [PATTERN] \dt[S+] [PATTERN] \dv[S+] [PATTERN]	In this group of commands, the letters E, i, s, t, and v stand for a foreign table, index, sequence, table, or view, respectively. You can specify any or a combination of these letters sequenced in any order to obtain an object list. For example, \dit lists all indexes and tables. If a command is suffixed with a plus sign (+), physical dimensions and related descriptions of each object will be displayed. Note This version does not support sequences.	If PATTERN is specified, only objects whose names match PATTERN are displayed. By default, only objects you created are displayed. You can specify PATTERN or S to view other system objects.	Lists all indexes and views. \div
\dx[+] [PATTERN]	Lists installed extensions.	If PATTERN is specified, only extensions whose names match PATTERN are displayed.	Lists installed extensions. \dx
\l[+]	Lists the names, owners, character set encoding, and permissions of all databases on the server.	`-`	Lists the names, owners, character set encoding, and permissions of all databases on the server. \l
\sf[+] FUNCNAME	Shows function definitions. Note If the function name contains parentheses, enclose the function name with quotation marks and add the parameter type list following the double quotation marks. Also enclose the list with parentheses.	`-`	Assume a function function_a and a function func()name. This parameter will be as follows: \sf function_a \sf "func()name"(argtype1, argtype2)
\z [PATTERN]	Lists all tables, views, and sequences in the database and their access permissions.	If a pattern is given, it is a regular expression, and only matched tables, views, and sequences are displayed.	Lists all tables, views, and sequences in the database and their access permissions. \z

**Table 5** Permission descriptions¶
Parameter	Description
r	SELECT: allows users to read data from specified tables and views.
w	UPDATE: allows users to update columns for specified tables.
a	INSERT: allows users to insert data to specified tables.
d	DELETE: allows users to delete data from specified tables.
D	TRUNCATE: allows users to delete all data from specified tables.
x	REFERENCES: allows users to create foreign key constraints.
t	TRIGGER: allows users to create a trigger on specified tables.
X	EXECUTE: allows users to use specified functions and the operators that are realized by the functions.
U	USAGE: For procedural languages, allows users to specify a procedural language when creating a function. For schemas, allows users to access objects includes in specified schemas. For sequences, allows users to use the nextval function.
C	CREATE: For databases, allows users to create schemas within a database. For schemas, allows users to create objects in a schema. For tablespaces, allows users to create tables in a tablespace and set the tablespace to default one when creating databases and schemas.
c	CONNECT: allows users to access specified databases.
T	TEMPORARY: allows users to create temporary tables.
A	ANALYZE\|ANALYSE: allows users to analyze tables.
L	ALTER: allows users to modify tables or schemas.
P	DROP: allows users to delete tables or schemas.
v	VACUUM: allows users to perform VACUUM on tables.
arwdDxtA, vLP	ALL PRIVILEGES: grants all available permissions to specified users or roles at a time. The table-level permissions ALTER, DROP, and VACUUM and schema permissions ALTER and DROP are added to the vLP permission group in cluster versions 8.1.3 and later.
*	Authorization options for preceding permissions

**Table 6** Formatting meta-commands¶
Parameter	Description
\a	Controls the switchover between unaligned mode and aligned mode.
\C [STRING]	Sets the title of any table being printed as the result of a query or cancels such a setting.
\f [STRING]	Sets a field separator for unaligned query output.
\H	If the text format schema is used, switches to the HTML format. If the HTML format schema is used, switches to the text format.
\pset NAME [VALUE]	Sets options affecting the output of query result tables. For details about the value of NAME, see Table 7.
\t [on\|off]	Switches the information and row count footer of the output column name.
\T [STRING]	Specifies attributes to be placed within the table tag in HTML output format. If the parameter is not configured, the attributes are not set.
\x [on\|off\|auto]	Switches expanded table formatting modes.

**Table 7** Adjustable printing options¶
Option	Description	Value Range
border	The value must be a number. In general, a larger number indicates wider borders and more table lines.	The value is an integer greater than 0 in HTML format. The value range in other formats is as follows: 0: no border 1: internal dividing line 2: table frame
expanded (or x)	Switches between regular and expanded formats.	When expanded format is enabled, query results are displayed in two columns, with the column name on the left and the data on the right. This format is useful if the data does not fit the screen in the normal "horizontal" format. The expanded format is used when the query output is wider than the screen. Otherwise, the regular format is used. The regular format is effective only in the aligned and wrapped formats.
fieldsep	Specifies the field separator to be used in unaligned output format. In this way, you can create tab- or comma-separated output required by other programs. To set a tab as field separator, type \pset fieldsep '\t'. The default field separator is a vertical bar ('\|').	`-`
fieldsep_zero	Sets the field separator to be used in unaligned output format to zero bytes.	`-`
footer	Enables or disables the display of table footers.	`-`
format	Selects the output format. Unique abbreviations are allowed. (That means a single letter is sufficient.)	Value range: unaligned: Write all columns of a row on one line, separated by the currently active column separator. aligned: This format is standard and human-readable. wrapped: This format is similar to aligned, but includes the packaging cross-line width data value to suit the width of the target field output. html: This format output table to the markup language for a document. The output is not a complete document. latex: This format output table to the markup language for a document. The output is not a complete document. troff-ms: This format output table to the markup language for a document. The output is not a complete document.
null	Sets a character string to be printed in place of a null value.	By default, nothing is printed, which can easily be mistaken for an empty character string.
numericlocale	Enables or disables the display of a locale-specific character to separate groups of digits to the left of the decimal marker.	on: The specified separator is displayed. off: The specified separator is not displayed If this parameter is ignored, the default separator is displayed.
pager	Controls the use of a pager for query and gsql help outputs. If the PAGER environment variable is set, the output is piped to the specified program. Otherwise, a platform-dependent default is used.	on: The pager is used for terminal output that does not fit the screen. off: The pager is not used. always: The pager is used for all terminal output regardless of whether it fits the screen.
recordsep	Specifies the record separator to be used in unaligned output format.	`-`
recordsep_zero	Specifies the record separator to be used in unaligned output format to zero bytes.	`-`
tableattr (or T)	Specifies attributes to be placed inside the HTML table tag in HTML output format (such as cellpadding or bgcolor). Note that you do not need to specify border here because it has been used by \pset border. If no value is given, the table attributes do not need to be set.	`-`
title	Specifies the table title for any subsequently printed tables. This can be used to give your output descriptive tags. If no value is given, the title does not need to be set.	`-`
tuples_only (or t)	Enables or disables the tuples-only mode. Full display may show extra information, such as column headers, titles, and footers. In tuples-only mode, only the table data is displayed.	`-`

**Table 8** Connection meta-commands¶
Parameter	Description	Value Range
\c[onnect] [DBNAME\|- USER\|- HOST\|- PORT\|-]	Connects to a new database. (The current database is gaussdb.) If a database name contains more than 63 bytes, only the first 63 bytes are valid and are used for connection. However, the database name displayed in the gsql CLI is still the name before the truncation. Note If the database login user is changed during reconnection, you need to enter the password of the new user. The maximum length of the password is 999 bytes, which is restricted by the maximum value of the GUC parameter password max length.	`-`
\encoding [ENCODING]	Sets the client character set encoding.	This command shows the current encoding if it has no parameter.
\conninfo	Outputs information about the current database connection.	`-`

**Table 9** OS meta-commands¶
Parameter	Description	Value Range
\cd [DIR]	Changes the current working directory.	An absolute path or relative path that meets the OS path naming convention
\setenv NAME [VALUE]	Sets the NAME environment variable to VALUE. If VALUE is not provided, do not set the environment variable.	`-`
\timing [on\|off]	Toggles a display of how long each SQL statement takes, in milliseconds.	The value on indicates that the setting is enabled. The value off indicates that the setting is disabled.
\! [COMMAND]	Escapes to a separate Unix shell or runs a Unix command.	`-`

**Table 10** Variable meta-commands¶
Parameter	Description
\prompt [TEXT] NAME	Prompts the user to use texts to specify a variable name.
\set [NAME [VALUE]]	Sets the NAME internal variable to VALUE. If more than one value is provided, NAME is set to the concatenation of all of them. If only one parameter is provided, the variable is set with an empty value. Some common variables are processed in another way in gsql, and they are the combination of uppercase letters, numbers, and underscores. Table 11 describes a list of variables that are processed in a way different from other variables.
\set-multi NAME [VALUE] \end-multi	Sets the internal variable NAME to VALUE that can consist of multiple lines of character strings. When \set-multi is used, the second parameter must be provided. For details, see the following example of using the \set-multi meta-command. Note The meta-commands in \set-multi and \end-multi will be ignored.
\unset NAME	Deletes the variable name of gsql.

\set-multi meta-command example

Sample file test.sql:

\set-multi multi_line_var
select
    id,name
from
    student;
\end-multi
\echo multi_line_var is "${multi_line_var}"
\echo -------------------------
\echo result is
${multi_line_var}

gsql -d gaussdb -p 25308 --dynamic-param -f test.sql execution result:

multi_line_var is "select
        id,name
from
        student; "
-------------------------
result is
 id | name
----+-------
  1 | Jack
  2 | Tom
  3 | Jerry
  4 | Danny
(4 rows)

Run the \set-multi \end-multi command to set the variable multi_line_var to one SQL statement and obtain the variable through dynamic variable parsing.

Sample file test.sql:

\set-multi multi_line_var
select 1 as id;
select 2 as id;
\end-multi
\echo multi_line_var is "${multi_line_var}"
\echo -------------------------
\echo result is
${multi_line_var}

gsql -d -p 25308 --dynamic-param -f test.sql execution result:

multi_line_var is "select 1 as id;
select 2 as id;"
-------------------------
result is
 id
----
  1
(1 row)

 id
----
  2
(1 row)

Run the \set-multi \end-multi command to set the variable multi_line_var to two SQL statements and obtain the variable through dynamic variable parsing. Because the content in the variable ends with a semicolon (;), gsql sends the SQL statement and obtains the printed execution result.

**Table 11** Common \set commands¶
Command	Description	Value Range
\set VERBOSITY value	This variable can be set to default, verbose, or terse to control redundant lines of error reports.	Value range: default, verbose, terse
\set ON_ERROR_STOP value	If this variable is set, the script execution stops immediately. If this script is invoked from another script, that script will be stopped immediately as well. If the primary script is invoked using the -f option rather than from one gsql session, gsql will return error code 3, indicating the difference between the current error and critical errors. (The error code for critical errors is 1.)	Value range: on/off, true/false, yes/no, 1/0
\set RETRY [retry_times]	Determines whether to enable the retry function if statement execution encounters errors. The parameter retry_times specifies the maximum number of retry times and the default value is 5. Its value ranges from 5 to 10. If the retry function has been enabled, when you run the \set RETRY command again, the retry function will be disabled. The configuration file retry_errcodes.conf shows a list of errors. If these errors occur, retry is required. This configuration file is placed in the same directory as that for executable gsql programs. This configuration file is configured by the system rather than by users and cannot be modified by the users. The retry function can be used in the following 13 error scenarios: YY001: TCP communication errors. Print information: Connection reset by peer. (reset between CN and DN) YY002: TCP communication errors. Print information: Connection reset by peer. (reset between DN and DN) YY003: Lock timeout. Print information: Lock wait timeout.../wait transaction xxx sync time exceed xxx. YY004: TCP communication errors. Print information: Connection timed out. YY005: Failed to issue SET commands. Print information: ERROR SET query. YY006: Failed to apply for memory. Print information: memory is temporarily unavailable. YY007: Communication library error. Print information: Memory allocate error. YY008: Communication library error. Print information: No data in buffer. YY009: Communication library error. Print information: Close because release memory. YY010: Communication library error. Print information: TCP disconnect. YY011: Communication library error. Print information: SCTP disconnect. YY012: Communication library error. Print information: Stream closed by remote. YY013: Communication library error. Print information: Wait poll unknown error. YY014: Invalid snapshot. Print information: Snapshot invalid. YY015: Failed to receive connection. Print information: Connection receive wrong. 53200: Out of memory. Print information: Out of memory. 08006: GTM error. Print information: Connection failure. 08000: Failed to communicate with DNs due to connection errors. Print information: Connection exception. 57P01: System shutdown by administrators. Print information: Admin shutdown. XX003: Remote socket is disabled. Print information: Stream remote close socket. XX009: Duplicate query IDs. Print information: Duplicate query id. YY016: Concurrent stream query and update. Print information: Stream concurrent update. CG003: Memory allocation error. Print information: Allocate Error. CG004: Fatal error. Print information: Fatal error. F0011: Temporary file reading error. Print information: File error. If an error occurs, gsql queries connection status of all CNs and DNs. If the connection status is abnormal, gsql sleeps for 1 minute and tries again. In this case, the retries in most of the primary/standby switchover scenarios are involved. Note Statements in transaction blocks cannot be retried upon a failure. Retry is not supported if errors are found using ODBC or JDBC. For SQL statements with unlogged tables, the retry is not supported if a node is faulty. If a CN or GTM is faulty, the retry on the gsql client is not supported. For gsql client faults, the retry is not supported.	Value range of retry_times: 5 to 10

**Table 12** Large object meta-commands¶
Parameter	Description
\lo_list	Displays all GaussDB(DWS) large objects currently stored in the database and their descriptions.

**Table 13** Flow control meta-commands¶
Parameter	Description	Value Range
\if EXPR \elif EXPR \else \endif	This set of meta-commands can implement nested conditional blocks: A conditional block starts with \if and ends with \endif. Any number of \elif clauses or a single \else clause can exist between \if and \endif. The \if and \elif commands support Boolean expression calculations and can check whether two strings are equal. \elif cannot be used between \else and \endif.	The Boolean expression calculation is the same as that of gsql: true/false, yes/no, on/off, and 1/0. Any other value is considered as true. The operator and the string must be separated using spaces. Number comparison and string comparison are supported. The comparison rules are controlled by the inherent variable COMPARE_STRATEGY. For details, see Table 2. In the default comparison rule, single quotation marks (') are used to separate strings and numbers. For details about the examples of different rules, see if conditional block comparison rules and examples. The following operators can be used to compare the values of numbers and strings and perform equality comparison: <, <=, >, >=, ==, !=, and <>.
\goto LABEL \label LABEL	This set of meta-commands can be used to implement unconditional redirections: The \label meta-command is used to create a label. The \goto meta-command is used to redirect upward or downward. Note The interactive mode is not supported. The \label meta-command is not supported in the \if statement block. To avoid unexpected results, you are not advised to use the \goto and \label commands in transactions or PL/SQL statement blocks.	The label value is case sensitive. The value of a label can contain uppercase letters, lowercase letters, digits, and underscores (_). The maximum length of a label is 32 characters (including \0). If the length exceeds 32 characters, the label will be truncated and an alarm will be generated. If the label name following \label appears repeatedly in the same session, an error is reported.
\for \loop \exit-for \end-for	This set of meta-commands can be used to implement loops: The loop block starts with \for and ends with \end-for. The condition between \for and \loop is a loop condition. Only SQL statements are supported. Variable iteration is not supported. For example, \for (i=0; i<100; ++i) is not supported. If there are multiple SQL statements in the loop condition, the execution result of the last SQL statement is used as the loop condition. The SQL statement used as a loop condition cannot end with a semicolon (;). The loop body exists between \loop and \end-for. You can run \exit-for to exit the loop. The \for loop block supports multi-layer nesting. Note The interactive mode is not supported. \for loop blocks cannot be used in \parallel. To avoid unexpected results, you are not advised to use the \for loop block in transactions or PL/SQL statement blocks. The \label meta-command is not supported in the \for loop block. Anonymous blocks cannot be used between \for and \loop.	`-`

An example of using flow control meta-commands is as follows:

\if conditional block use example:
Sample file test.sql:
```
SELECT 'Jack' AS "Name";

\if ${ERROR}
    \echo 'An error occurred in the SQL statement'
    \echo ${LAST_ERROR_MESSAGE}
\elif '${Name}' == 'Jack'
    \echo 'I am Jack'
\else
    \echo 'I am not Jack'
\endif
```
gsql -d -p 25308 --dynamic-param -f test.sql execution result:
```
 Name
------
 Jack
(1 row)

I am Jack
```
The preceding execution result indicates that the first SQL statement is successfully executed and the Name variable is set. Therefore, the \elif branch is executed and the output is I am Jack. For details about the usage of the special variables ERROR and LAST_ERROR_MESSAGE, see Table 2.
\if conditional block comparison rules and examples
- default: Specifies the default comparison policy. Only strings or numbers can be compared, and strings cannot be compared with numbers. Parameters inside single quotation marks (') are identified as strings, and parameters outside single quotation marks (') are identified as numbers.
The file test.sql is used as an example.
```
\set Name 'Jack'
\set ID 1002

-- Parameters inside single quotation marks (') are identified as strings for comparison.
\if '${Name}' != 'Jack'
    \echo 'I am not Jack'
-- Without single quotation marks ('), parameters are identified as numbers for comparison.
\elif ${ID} > 1000
    \echo 'Jack\'id is bigger than 1000'
\else
    \echo 'error'
\endif
```
gsql -d -p 25308 --dynamic-param -f test.sql execution result:
```
Jack'id is bigger than 1000
```
If a single quotation mark (') is used on one side of the operator and not used on the other side, the comparison is performed between a string and a number. Such comparison is not supported and an error is reported.
```
postgres=> \set Name 'Jack'
postgres=> \if ${Name} == 'Jack'
ERROR: left[Jack] is a string without quote or number, and right['Jack'] is a string with quote, \if or \elif does not support this expression.
WARNING: The input with quote are treated as a string, and the input without quote are treated as a number.
postgres@> \endif
```
- natural: The default comparison policy is supported, and parameters that contain dynamic variables are also identified as strings. When one side of the comparison operator is a number, try to convert the other side to a number, and then compare the numbers on both sides. If the conversion fails, an error is reported and the comparison result is false.
  - Parameters that contain dynamic variables can be identified as strings when single quotation marks (') are used, for example, 'Jack', or the string contains a dynamic variable (${VAR} or :VAR), for example, ${Name}_data. If both of the preceding conditions are met, for example, '${Name}_data', parameters that contain dynamic variables can also be identified as strings.
  - For parameters that cannot be identified as strings, try to identify them as numbers. If a parameter cannot be converted to a number, an error is reported. For example, 1011Q1 does not use single quotation marks (') or contain dynamic variables, and cannot be converted to a number.
  - If one side of the comparison operator is not identified as a string or number, the comparison fails and an error is reported.
  - If one side of the comparison operator is identified as a number, the comparison is performed based on numbers. If the other side cannot be converted to a number, an error is reported.
  - If both sides of the comparison operator are identified as strings, the comparison is performed based on strings.
The test.sql file is an example of comparing strings.
```
\set COMPARE_STRATEGY natural
SELECT 'Jack' AS "Name";

-- The comparison result is equivalent to that of '${Name}' > 'Jack'.
\if ${Name} == 'Jack'
    \echo 'I am Jack'
\else
    \echo 'I am not Jack'
\endif
```
gsql -d -p 25308 --dynamic-param -f test.sql execution result:
```
 Name
------
 Jack
(1 row)

I am Jack
```
The test.sql file is an example of comparing numbers.
```
\set COMPARE_STRATEGY natural
SELECT 1022 AS id;

-- If ${id} == '01022' is used, the result is not equal because strings on both sides are compared.
\if ${id} == 01022
    \echo 'id is 1022'
\else
    \echo 'id is not 1022'
\endif
```
gsql -d -p 25308 --dynamic-param -f test.sql execution result:
```
  id
------
 1022
(1 row)

id is 1022
```
Examples of comparison errors are shown as follows.
```
-- One side of the operator cannot be identified as a string or number.
postgres=> \set COMPARE_STRATEGY natural
postgres=> \if ${Id} > 123sd
ERROR: The right[123sd] can not be treated as a string or a number. A numeric string should contain only digits and one decimal point, and a string should be enclosed in quote or contain dynamic variables, please check it.
-- Numbers on one side of the operator cannot be correctly converted.
postgres=> \set COMPARE_STRATEGY natural
postgres=> \if ${Id} <> 11101.1.1
ERROR: The right[11101.1.1] can not be treated as a string or a number. A numeric string should contain only digits and one decimal point, and a string should be enclosed in quote or contain dynamic variables, please check it.
```
- equal: Only the equality comparison is supported. The comparison is performed based on strings.
The file test.sql is used as an example.
```
\set COMPARE_STRATEGY equal
SELECT 'Jack' AS "Name";

\if ${ERROR}
    \echo 'An error occurred in the SQL statement'
-- If the value is set to equal, only the equality comparison is supported. An error is reported when the values are compared, and there is no delimiter. The following comparison result is equivalent to that of ${Name} == Jack.
\elif '${Name}' == 'Jack'
    \echo 'I am Jack'
\else
    \echo 'I am not Jack'
\endif
```
gsql -d -p 25308 --dynamic-param -f test.sql execution result:
```
 Name
------
 Jack
(1 row)

I am Jack
```
\goto \label redirection example:
Sample file test.sql:
```
\set Name Tom

\goto TEST_LABEL
SELECT 'Jack' AS "Name";

\label TEST_LABEL
\echo ${Name}
```
gsql -d -p 25308 --dynamic-param -f test.sql execution result:
```
Tom
```
The preceding execution result indicates that the \goto meta-command directly executes the \echo command without re-assigning a value to the variable Name.
Example of using \if conditional block and the \goto \label together
Sample file test.sql:
```
\set Count 1

\label LOOP
\if ${Count} != 3
    SELECT ${Count} + 1 AS "Count";
    \goto LOOP
\endif

\echo Count = ${Count}
```
gsql -d -p 25308 --dynamic-param -f test.sql execution result:
```
 Count
-------
     2
(1 row)

 Count
-------
     3
(1 row)

Count = 3
```
The preceding execution result indicates that a simple loop is implemented through the combination of the \if conditional block and \goto \label.

Example of Using \for Loop Blocks

To demonstrate this function, the example data is as follows:

create table student (id int, name varchar(32));
insert into student values (1, 'Jack');
insert into student values (2, 'Tom');
insert into student values (3, 'Jerry');
insert into student values (4, 'Danny');

create table course (class_id int, class_day varchar(5), student_id int);
insert into course values (1004, 'Fri', 2);
insert into course values (1003, 'Tue', 1);
insert into course values (1003, 'Tue', 4);
insert into course values (1002, 'Wed', 3);
insert into course values (1001, 'Mon', 2);

\for loop use sample file test.sql:

\for
select id, name from student order by id limit 3 offset 0
\loop
    \echo -[ RECORD ]+-----
    \echo id '\t'| ${id}
    \echo name '\t'| ${name}
\end-for

gsql -d -p 25308 --dynamic-param -f test.sql execution result:

-[ RECORD ]+-----
id      | 1
name    | Jack
-[ RECORD ]+-----
id      | 2
name    | Tom
-[ RECORD ]+-----
id      | 3
name    | Jerry

The preceding execution result indicates that the loop block is used to traverse the execution result of the SQL statement. More statements can appear between \loop and \end-for to implement complex logic.

If the SQL statement used as a loop condition fails to be executed or the result set is empty, the statement between \loop and \end-for will not be executed.

Sample file test.sql:

\for
select id, name from student_error order by id limit 3 offset 0
\loop
    \echo -[ RECORD ]+-----
    \echo id '\t'| ${id}
    \echo name '\t'| ${name}
\end-for

gsql -d -p 25308 --dynamic-param -f test.sql execution result:

gsql:test.sql:3: ERROR:  relation "student_error" does not exist
LINE 1: select id, name from student_error order by id limit 3 offse...
                             ^

The preceding command output indicates that the student_error table does not exist. Therefore, the SQL statement fails to be executed, and the statement between \loop and \end-for is not executed.

\exit-for exits the loop.
Sample file test.sql:
```
\for
select id, name from student order by id
\loop
    \echo ${id} ${name}
    \if ${id} == 2
        \echo find id(2), name is ${name}
        \exit-for
    \endif
\end-for
```
gsql -d -p 25308 --dynamic-param -f test.sql execution result:
```
1 Jack
2 Tom
find id(2), name is Tom
```
If the student table contains more than two rows of data and id is set to 2, run the \exit-for command to exit the loop. This process is also used together with the \if condition block.

\for loop nesting

Sample file test.sql:

\for
select id, name from student order by id limit 2 offset 0
\loop
    \echo ${id} ${name}
    \for
    select
    class_id, class_day
    from course
    where student_id = ${id}
    order by class_id
    \loop
        \echo '  '${class_id}, ${class_day}
    \end-for
\end-for

gsql -d -p 25308 --dynamic-param -f test.sql execution result:

1 Jack
  1003, Tue
2 Tom
  1001, Mon
  1004, Fri

Obtain the information about Jack and Tom in the course table through the two-layer loop.

PATTERN¶

The various \d commands accept a PATTERN parameter to specify the object name to be displayed. In the simplest case, a pattern is just the exact name of the object. The characters within a pattern are normally folded to lower case, similar to those in SQL names. For example, \dt FOO will display the table named foo. As in SQL names, placing double quotation marks (") around a pattern prevents them being folded to lower case. If you need to include a double quotation mark (") in a pattern, write it as a pair of double quotation marks ("") within a double-quote sequence, which is in accordance with the rules for SQL quoted identifiers. For example, \dt "FOO""BAR" will be displayed as a table named FOO"BAR instead of foo"bar. You cannot put double quotation marks around just part of a pattern, which is different from the normal rules for SQL names. For example, \dt FOO"FOO"BAR will be displayed as a table named fooFOObar if just part of a pattern is quoted.

Whenever the PATTERN parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path, which is equivalent to using an asterisk (*) as the pattern. An object is regarded to be visible if it can be referenced by name without explicit schema qualification. To see all objects in the database regardless of their visibility, use a dot within double quotation marks (*.*) as the pattern.

Within a pattern, the asterisk (*) matches any sequence of characters (including no characters) and a question mark (?) matches any single character. This notation is comparable to Unix shell file name patterns. For example, \dt int* displays tables whose names begin with int. But within double quotation marks, the asterisk (*) and the question mark (?) lose these special meanings and are just matched literally.

A pattern that contains a dot (.) is interpreted as a schema name pattern followed by an object name pattern. For example, \dt foo*.*bar* displays all tables (whose names include bar) in schemas starting with foo. If no dot appears, then the pattern matches only visible objects in the current schema search path. Again, a dot within double quotation marks loses its special meaning and is matched literally.

Advanced users can use regular-expression notations, such as character classes. For example [0-9] can be used to match any digit. All regular-expression special characters work as specified in "POSIX regular expressions" in the Developer Guide, except the following characters:

A dot (.) is used as a separator.
An asterisk (*) is translated into an asterisk prefixed with a dot (.*), which is a regular-expression marking.
A question mark (?) is translated into a dot (.).
A dollar sign ($) is matched literally.

You can write ?, (R+|), (R|), and R to the following pattern characters: ., R*, and R?. The dollar sign ($) does not need to work as a regular-expression character since the pattern must match the whole name, which is different from the usual interpretation of regular expressions. In other words, the dollar sign ($) is automatically appended to your pattern. If you do not expect a pattern to be anchored, write an asterisk (*) at its beginning or end. All regular-expression special characters within double quotation marks lose their special meanings and are matched literally. Regular-expression special characters in operator name patterns (such as the \do parameter) are also matched literally.

last updated: 2024-12-11 14:09 UTC - commit: 68b0697a99ca2a4acec84bb31c0962598d3b4a14