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) |
getdistributekey(table_name)
Description: Gets a distribution key for a hash table. Distribution is not supported in a standalone system and the return value of this function is empty.
pg_check_authid(role_oid)
Description: Checks whether a role name with given OID exists.
Return type: Boolean
Example:
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 system 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 might contain keywords, specify the OID of the relationship they refer to as the second parameter; if no keywords are expected, zero is sufficient.
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. For an interval partitioned table that contains a local index, if dump_schema_only is set to true, the returned index creation statement does not contain the local index information of the automatically created partition. If dump_schema_only is set to false, the returned index creation statement contains the local index information of the automatically created partition. For a non-interval partitioned table or an interval partitioned table that does not contain a local index, the value of dump_schema_only does not affect the returned result of the function.
Return type: text
gaussdb=# CREATE TABLE sales (prod_id NUMBER(6), cust_id NUMBER, time_id DATE, channel_id CHAR(1), promo_id NUMBER(6), quantity_sold NUMBER(3), amount_sold NUMBER(10,2)) PARTITION BY RANGE( time_id) INTERVAL('1 day') (partition p1 VALUES LESS THAN ('2019-02-01 00:00:00'), partition p2 VALUES LESS THAN ('2019-02-02 00:00:00') ); CREATE TABLE gaussdb=# CREATE INDEX index_sales ON sales(prod_id) local (PARTITION idx_p1 ,PARTITION idx_p2); CREATE INDEX gaussdb=# -- If the data to be inserted does not match any partition, create a partition and insert the data into the new partition. gaussdb=# INSERT INTO sales VALUES(1, 12, '2019-02-05 00:00:00', 'a', 1, 1, 1); INSERT 0 1 gaussdb=# SELECT oid FROM pg_class WHERE relname = 'index_sales'; oid ------- 24632 (1 row) gaussdb=# SELECT * FROM pg_get_indexdef(24632, true); pg_get_indexdef -------------------------------------------------------------------------------------------------------------------------- CREATE INDEX index_sales ON sales USING btree (prod_id) LOCAL(PARTITION idx_p1, PARTITION idx_p2) TABLESPACE pg_default (1 row) gaussdb=# SELECT * FROM pg_get_indexdef(24632, false); pg_get_indexdef ------------------------------------------------------------------------------------------------------------------------------------ -------------------- CREATE INDEX index_sales ON sales USING btree (prod_id) LOCAL(PARTITION idx_p1, PARTITION idx_p2, PARTITION sys_p1_prod_id_idx) TA BLESPACE 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.
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)
Return type: text
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_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
Example:
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.
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, storage_type=USTORE, segment=off); +
ALTER TABLE t1 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION;
(1 row)
Return type: text
pg_get_tabledef(table_name)
Description: Obtains a table definition based on table_name.
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, storage_type=USTORE, segment=off); +
ALTER TABLE t1 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION;
(1 row)
Return type: text
Note: pg_get_tabledef reconstructs the CREATE statement of the table definition, including the table definition, index information, comments, and ILM policy (if exists). You need to separately create the dependent objects of the table, such as groups, schemas, tablespaces, and servers. The table definition does not include the statements for creating these dependent objects. 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 system catalogs.
pg_tablespace_location(tablespace_oid)
Description: Obtains the path in the file system that this tablespace is located in. When this function is called in a non-PDB in the multi-tenancy scenario, the file path of the non-PDB tablespace is returned. When this function is called in a PDB in the multi-tenancy scenario, the file path of the PDB tablespace is returned.
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 return type of this function is regtype, which is an OID alias type (see Object Identifier Types). 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.
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 is raised.
Example:
1 2 3 4 5 |
gaussdb=# SELECT collation for (description) FROM pg_description LIMIT 1; pg_collation_for ------------------ "default" (1 row) |
pg_extension_update_paths(name)
Description: Returns the version update path of the specified extension.
Return type: text(source text), text(path text), text(target 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)
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
