HomeMogDBMogDB StackUqbar
Ecological Tools
Doc Tools
中文
v5.0

Documentation:v5.0

Supported Versions:
Current(v5.0)/
v3.1

Other Versions:
v3.0/
v2.1/
v2.0/
v1.1

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 that is identified by its type OID and possibly a type modifier. Pass NULL for the type modifier if no specific modifier is known. Certain type modifiers are passed for data types with length limitations. The SQL name returned from 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. So the actual storage length contains 4 bytes more than the customized length. In the following example, the SQL name returned from format_type is character varying(6), indicating the length of varchar type is 6 bytes. So the actual storage length of varchar type is 10 bytes.

    MogDB=# SELECT format_type((SELECT oid FROM pg_type WHERE typname='varchar'), 10);
     format_type
----------------------
 character varying(6)
(1 row)

  • getdistributekey(table_name)

    Description: Obtains a distribution column 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 a given OID exists.

    Return type: Boolean

    Example:

    MogDB=# 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 a description of a database object specified by catalog OID, object OID and a (possibly zero) sub-object ID. This is useful to determine the identity of an object as stored in the pg_depend catalog.

  • pg_get_constraintdef(constraint_oid)

    Description: Obtains the definition of a constraint.

    Return type: text

  • pg_get_constraintdef(constraint_oid, pretty_bool)

    Description: Obtains the definition of a constraint.

    Return type: text

    Note: pg_get_constraintdef and pg_get_indexdef respectively reconstruct the creating command for a constraint and an index.

  • pg_get_expr(pg_node_tree, relation_oid)

    Description: Decompiles 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 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 for a column. It can be useful when examining the contents of system catalogs. If the expression might contain Vars, specify the OID of the relationship they refer to as the second parameter; if no Vars are expected, zero is sufficient.

  • pg_get_functiondef(func_oid)

    Description: Obtains the definition of a function.

    Return type: text

    Example:

    MogDB=# 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 the parameter list of the 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 within 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

    Example:

    MogDB=# 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

    Example:

    MogDB=# CREATE TABLE sales
mogdb-# (prod_id NUMBER(6),
mogdb(#  cust_id NUMBER,
mogdb(#  time_id DATE,
mogdb(#  channel_id CHAR(1),
mogdb(#  promo_id NUMBER(6),
mogdb(#  quantity_sold NUMBER(3),
mogdb(#  amount_sold NUMBER(10,2)
mogdb(# )
PARTITION BY RANGE( time_id) INTERVAL('1 day')
mogdb-# mogdb-# (
mogdb(#  partition p1 VALUES LESS THAN ('2019-02-01 00:00:00'),
mogdb(#  partition p2 VALUES LESS THAN ('2019-02-02 00:00:00')
mogdb(# );
CREATE TABLE
MogDB=# create index index_sales on sales(prod_id) local (PARTITION idx_p1 ,PARTITION idx_p2);
CREATE INDEX
MogDB=#-- If the data to be inserted does not match any partition, create a partition and insert the data into the new partition.
MogDB=# INSERT INTO sales VALUES(1, 12, '2019-02-05 00:00:00', 'a', 1, 1, 1);
INSERT 0 1
MogDB=# select oid from pg_class where relname = 'index_sales';
  oid
-------
 24632
(1 row)
MogDB=# 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)
MogDB=# 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 column_no is not zero.

    Example:

    MogDB=# 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)
MogDB=# 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.

    Return type: SETOF record

    Note: pg_get_keywords returns a set of records describing the SQL keywords recognized by the server. The word column contains the keyword. 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.

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

    MogDB=# 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 variants. When the function has the parameter pretty_bool and the value is true, it can optionally "pretty-print" the result. The pretty-printed format is more readable. The other one is default format which is more likely to be interpreted the same way by future versions of PostgreSQL. Avoid using pretty-printed output for dump purposes. Passing false for the pretty-print parameter yields the same result as the variant that does not have the parameter at all.

  • 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, wrapping lines with columns as specified, printing is implied.

    Return type: text

  • pg_get_ori_viewdef(view_name)

    Description: Obtains the original DDL command for a view. (Since MogDB 5.0.2)

    Return type: text

    Example：

    MogDB=# select pg_get_ori_viewdef ('v1');
             pg_get_ori_viewdef
---------------------------------------------
  CREATE OR REPLACE VIEW public.v1(a, b) AS +
     /*line as */   --line as               +
 select  /* line select */  --line select   +
 *    /* line target */  --line target      +
 from  /* line from */  --line from         +
 t_auto    /* line rel  */  --line rel      +
 ;
(1 row)

  • pg_get_tabledef(table_oid)

    Description: Obtains a table definition based on table_oid.

    Example:

    MogDB=# 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)               +
 TO GROUP group1;
(1 row)

    Return type: text

  • pg_get_tabledef(table_name)

    Description: Obtains a table definition based on table_name.

    Example:

    MogDB=# 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)               +
 TO GROUP group1;
(1 row)

    Return type: text

    Remarks: pg_get_tabledef reconstructs the CREATE statement of the table definition, including the table definition, index information, and comments. Users need to 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.

  • 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 passed pg_class.reloptions or pg_attribute.attoptions.

  • 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 examined. It returns the set of OIDs of databases that have objects stored in the tablespace. If this function returns any rows, the tablespace is not empty and cannot be dropped. To display the specific objects populating the tablespace, you will 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. The function is declared as returning regtype, which is an OID alias type (see OID Types). This means that it is the same as an OID for comparison purposes but displays as a type name.

    Example:

    MogDB=# SELECT pg_typeof(33);
 pg_typeof
-----------
 integer
(1 row)

MogDB=# 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:

    MogDB=# 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 argument expression, then a null value is returned. If the parameter is not of a collectable data type, then an error is thrown.

  • 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

    Example:

    MogDB=# 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

    Example:

    MogDB=# select * from pg_sequence_parameters(16420);
 start_value | minimum_value |    maximum_value    | increment | cycle_option
-------------+---------------+---------------------+-----------+--------------
         101 |             1 | 9223372036854775807 |         1 | f
(1 row)
HOME

Issue
Copyright © 2011-2024 www.enmotech.com All rights reserved.