System Catalog Information Functions
format_type(type_oid, typemod)
Description: Obtains the SQL name of a data type.
Return type: text
Note: format_type returns the SQL name of a data type based on the OID of the data type and possible modifiers. If the specific modifier is unknown, pass NULL at the position of the modifier. Modifiers are generally meaningful only for data types with length restrictions. The SQL name returned by format_type contains the length of the data type, which can be calculated by taking sizeof(int32) from actual storage length [actual storage len – sizeof(int32)] in the unit of bytes. 32-bit space is required to store the customized length set by users. Therefore, the actual storage length contains 4 bytes more than the customized length. In the following example, the SQL name returned by format_type is character varying(6), indicating the length of the varchar type is 6 bytes. Therefore, the actual storage length of the varchar type is 10 bytes.
1 2 3 4 5 |
gaussdb=# SELECT format_type((SELECT oid FROM pg_type WHERE typname='varchar'), 10); format_type ---------------------- character varying(6) (1 row) |
pg_check_authid(role_oid)
Description: Checks whether a role name with given OID exists.
Return type: Boolean
gaussdb=# select pg_check_authid(1);
pg_check_authid
-----------------
f
(1 row)
pg_describe_object(catalog_id, object_id, object_sub_id)
Description: Obtains the description of a database object.
Return type: text
Note: pg_describe_object returns the description of a database object specified by a catalog OID, an object OID, and a (possibly zero) sub-object ID. This is useful to determine the identity of an object stored in the pg_depend catalog.
pg_get_constraintdef(constraint_oid)
Description: Obtains definition of a constraint.
Return type: text
pg_get_constraintdef(constraint_oid, pretty_bool)
Description: Obtains definition of a constraint.
Return type: text
Note: pg_get_constraintdef and pg_get_indexdef respectively reconstruct the creation command for a constraint and an index.
pg_get_expr(pg_node_tree, relation_oid)
Description: Decompiles the internal form of an expression, assuming that any Vars in it refer to the relationship indicated by the second parameter.
Return type: text
pg_get_expr(pg_node_tree, relation_oid, pretty_bool)
Description: Decompiles the internal form of an expression, assuming that any Vars in it refer to the relationship indicated by the second parameter.
Return type: text
Note: pg_get_expr decompiles the internal form of an individual expression, such as the default value of a column. This helps to check the content of system catalogs. If the expression contains keywords, specify the OID of the relationship that the keywords refer to as the second parameter; if no keywords are contained, use zero.
pg_get_functiondef(func_oid)
Description: Obtains the definition of a function.
Return type: text
gaussdb=# SELECT * FROM pg_get_functiondef(598);
headerlines | definition
-------------+----------------------------------------------------
4 | CREATE OR REPLACE FUNCTION pg_catalog.abbrev(inet)+
| RETURNS text +
| LANGUAGE internal +
| IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE +
| AS $function$inet_abbrev$function$ +
|
(1 row)
pg_get_function_arguments(func_oid)
Description: Obtains argument list of function's definition (with default values).
Return type: text
Note: pg_get_function_arguments returns the parameter list of a function, in the form it would need to appear in CREATE FUNCTION.
pg_get_function_identity_arguments(func_oid)
Description: Obtains the parameter list to identify a function (without default values).
Return type: text
Note: pg_get_function_identity_arguments returns the parameter list necessary to identify a function, in the form it would need to appear in within ALTER FUNCTION. This form omits default values.
pg_get_function_result(func_oid)
Description: Obtains the RETURNS clause for a function.
Return type: text
Note: pg_get_function_result returns the appropriate RETURNS clause for the function.
pg_get_indexdef(index_oid)
Description: Obtains the CREATE INDEX command for an index.
Return type: text
gaussdb=# SELECT * FROM pg_get_indexdef(16416);
pg_get_indexdef
-------------------------------------------------------------------------
CREATE INDEX test3_b_idx ON test3 USING btree (b) TABLESPACE pg_default
(1 row)
pg_get_indexdef(index_oid, dump_schema_only)
Description: Obtains the CREATE INDEX command for indexes in dump scenarios. In the current version, the value of dump_schema_only does not affect the function output.
Return type: text
gaussdb=# SELECT * FROM pg_get_indexdef(16416, true);
pg_get_indexdef
-------------------------------------------------------------------------
CREATE INDEX test3_b_idx ON test3 USING btree (b) TABLESPACE pg_default
(1 row)
pg_get_indexdef(index_oid, column_no, pretty_bool)
Description: Obtains the CREATE INDEX command for an index, or definition of just one index column when the value of column_no is not zero.
Return type: text
gaussdb=# SELECT * FROM pg_get_indexdef(16416, 0, false); pg_get_indexdef ------------------------------------------------------------------------- CREATE INDEX test3_b_idx ON test3 USING btree (b) TABLESPACE pg_default (1 row) gaussdb=# SELECT * FROM pg_get_indexdef(16416, 1, false); pg_get_indexdef ----------------- b (1 row)
Note: pg_get_functiondef returns a complete CREATE OR REPLACE FUNCTION statement for a function.
pg_get_keywords()
Description: Obtains the list of SQL keywords and their categories. The function returns the records of SQL keywords identified by the server. The columns are as follows:
- The word column contains the keywords.
- The catcode column contains a category code: U for unreserved, C for column name, T for type or function name, or R for reserved.
- The catdesc column contains a possibly-localized string describing the category.
Return type: SETOF record
Note: The keyword range and type vary depending on the compatibility mode, and the return result of the function also varies. The records returned by the function do not contain the options configured in the disable_keyword_options parameter. For details about disable_keyword_options, see "Configuring GUC Parameters > GUC Parameters > Version and Platform Compatibility > Platform and Client Compatibility" in Administrator Guide.
pg_get_ruledef(rule_oid)
Description: Obtains the CREATE RULE command for a rule.
Return type: text
pg_get_ruledef(rule_oid, pretty_bool)
Description: Obtains the CREATE RULE command for a rule.
Return type: text
pg_get_userbyid(role_oid)
Description: Obtains the role name with a given OID.
Return type: name
Note: pg_get_userbyid extracts a role's name given its OID.
pg_check_authid(role_id)
Description: Checks whether a user exists based on role_id.
Return type: text
gaussdb=# SELECT pg_check_authid(20);
pg_check_authid
-----------------
f
(1 row)
pg_get_viewdef(view_name)
Description: Obtains the underlying SELECT command for a view.
Return type: text
pg_get_viewdef(view_name, pretty_bool)
Description: Obtains the underlying SELECT command for a view. Lines with columns are wrapped to 80 columns if pretty_bool is set to true.
Return type: text
Note: pg_get_viewdef reconstructs the SELECT query that defines a view. Most of these functions come in two forms. When the function has the pretty_bool parameter and the value is true, it can optionally "pretty-print" the result. The pretty-printed format is more readable. The other one is the default format which is more likely to be interpreted in the same way by future versions. Avoid using pretty-printed output for dump purposes. Passing false to the pretty-print parameter generates the same result as a variant without this parameter.
pg_get_viewdef(view_oid)
Description: Obtains the underlying SELECT command for a view.
Return type: text
pg_get_viewdef(view_oid, pretty_bool)
Description: Obtains the underlying SELECT command for a view. Lines with columns are wrapped to 80 columns if pretty_bool is set to true.
Return type: text
pg_get_viewdef(view_oid, wrap_column_int)
Description: Obtains the underlying SELECT command for a view. Lines with columns are wrapped to the specified number of columns and printing is implicit.
Return type: text
pg_get_tabledef(table_oid)
Description: Obtains the definition of a table based on table_oid.
Return type: text
gaussdb=# SELECT * FROM pg_get_tabledef(16384);
pg_get_tabledef
-----------------------------------------------------------------------------------------------
SET search_path = public; +
CREATE TABLE t1 ( +
c1 bigint DEFAULT nextval('serial'::regclass) +
) +
WITH (orientation=row, compression=no) +
DISTRIBUTE BY HASH(c1) +
TO GROUP group1; +
ALTER TABLE t1 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION;
(1 row)
pg_get_tabledef(table_name)
Description: Obtains the definition of a table based on table_name.
Return type: text
gaussdb=# SELECT * FROM pg_get_tabledef('t1');
pg_get_tabledef
-----------------------------------------------------------------------------------------------
SET search_path = public; +
CREATE TABLE t1 ( +
c1 bigint DEFAULT nextval('serial'::regclass) +
) +
WITH (orientation=row, compression=no) +
DISTRIBUTE BY HASH(c1) +
TO GROUP group1; +
ALTER TABLE t1 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION;
(1 row)
Note: pg_get_tabledef reconstructs the CREATE statement of the definition of the table, including the table definition, index information, comments, and ILM policy. You need to create the group, schema, tablespace, and server information on which the table object depends. If the ILM policy is inherited from the parent object, pg_get_tabledef does not return the ILM policy creation statement of the data object.
pg_options_to_table(reloptions)
Description: Obtains the set of storage option name/value pairs.
Return type: SETOF record
Note: pg_options_to_table returns the set of storage option name/value pairs (option_name/option_value) when pg_class.reloptions or pg_attribute.attoptions is passed.
pg_tablespace_databases(tablespace_oid)
Description: Obtains the set of database OIDs that have objects in the specified tablespace.
Return type: setof oid
Note: pg_tablespace_databases allows a tablespace to be checked. It returns the set of OIDs of databases that have objects stored in the tablespace. If this function returns any rows of data, the tablespace is not empty and cannot be dropped. To display the specific objects in the tablespace, you need to connect to the databases identified by pg_tablespace_databases and query their pg_class catalogs.
pg_tablespace_location(tablespace_oid)
Description: Obtains the path in the file system that this tablespace is located in.
Return type: text
pg_typeof(any)
Description: Obtains the data type of any value.
Return type: regtype
Note: pg_typeof returns the OID of the data type of the value that is passed to it. This can be helpful for troubleshooting or dynamically constructing SQL queries. It is declared that the function returns regtype, which is an OID alias type (see Object Identifier Types). This means that it is the same as an OID for comparison purposes but displays as a type name.
Example:
1 2 3 4 5 6 7 8 9 10 11 |
gaussdb=# SELECT pg_typeof(33); pg_typeof ----------- integer (1 row) gaussdb=# SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); typlen -------- 4 (1 row) |
collation for (any)
Description: Obtains the collation of the parameter.
Return type: text
Note: The expression COLLATION FOR returns the collation of the value that is passed to it. Example:
1 2 3 4 5 |
gaussdb=# SELECT collation for (description) FROM pg_description LIMIT 1; pg_collation_for ------------------ "default" (1 row) |
The value might be quoted and schema-qualified. If no collation is derived for the parameter expression, then a null value is returned. If the argument is not of a collatable data type, then an error message is displayed.
getdistributekey(table_name)
Description: Gets a distribution key for a hash table.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT getdistributekey('item'); getdistributekey ------------------ i_item_sk (1 row) |
pg_extension_update_paths(name)
Description: Returns the version update path of the specified extension. This function can be called only by system administrators.
Return type: text (source text), text (target text), text (path text)
pg_get_serial_sequence(tablename, colname)
Description: Obtains the sequence of the corresponding table name and column name.
Return type: text
gaussdb=# SELECT * FROM pg_get_serial_sequence('t1', 'c1');
pg_get_serial_sequence
------------------------
public.serial
(1 row)
pg_sequence_parameters(sequence_oid)
Description: Obtains the parameters of a specified sequence, including the start value, minimum value, maximum value, and incremental value.
Return type: int16, int16, int16, int16, Boolean
gaussdb=# SELECT * FROM pg_sequence_parameters(16420);
start_value | minimum_value | maximum_value | increment | cycle_option
-------------+---------------+---------------------+-----------+--------------
101 | 1 | 9223372036854775807 | 1 | f
(1 row)
pgxc_get_variable_info( )
Description: Obtains variable values on the node, including nodeName, nextOid, nextXid, oldestXid, xidVacLimit, oldestXidDB, lastExtendCSNLogpage, startExtendCSNLogpage, nextCommitSeqNo, latestCompleteXid, and startupMaxXid.
Return type: set of pg_variable_info
gaussdb=# SELECT pgxc_get_variable_info( );
pgxc_get_variable_info
-------------------------------------------------------------------------
(dn_6004_6005_6006,25617,141396349,2073,20000002073,15808,138111,0,127154152,141396348,104433004)
(1 row)
gs_get_index_status(schema_name, index_name)
Description: Obtains the index status information on all nodes, including whether an index can be inserted and whether an index is available. This function is mainly used to check the index status during online index creation or when the index creation fails. The return values include node_name, indisready, and indisvalid. Only when indisready and indisvalid of indexes on all nodes are set to true and the index state is not changed to unusable, the current index is available.
Return type: text, Boolean, Boolean
gaussdb=# SELECT * FROM gs_get_index_status('public', 'index1');
node_name | indisready | indisvalid
----------------+------------+-----------
datanode1 | t | t
datanode2 | t | t
coordinator1 | t | t
(3 row)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
