System Catalog Information Functions

format_type(type_oid, typemod)

Description: Obtains the SQL name of a data type.

Return type: text.

Remarks:

format_type returns the SQL name based on the type OID of the data type and possible type modifiers. If the specific modifier is unknown, NULL is passed in the position of the type modifier. Generally, type modifiers are valid only for data types with length limitations. The SQL name returned by format_type contains the length value of the data type. The length value is the actual storage length len – sizeof (int32), in bytes. During data storage, 32-bit space is required to store the customized length information of the data type. That is, the actual storage length is four bytes longer than the customized length. In the following example, the SQL name returned by format_type is character varying (6), where 6 indicates that the length value of the varchar type is 6 bytes. Therefore, the actual storage length of this type is 10 bytes.

1
2
3
4
5

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

Pg_get_schemas(schemaPattern)

Description: Queries schemas in the current catalog. The schemaPattern parameter can be a schema name or a wildcard character represented by %. If schemaPattern is empty (using single quotes instead of parameters), it returns all schemas.

Return type: text.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

select pg_get_schemas('');
     pg_get_schemas
----------------------
 test_orc
 test1_orc
 test_parquet
(3 row)

select pg_get_schemas('%orc');
     pg_get_schemas
----------------------
 test_orc
 test1_orc
(2 row)

select pg_get_schemas('test_orc');
     pg_get_schemas
----------------------
 test_orc
(1 row)

pg_get_tables(schemaPattern, tablePattern, tableTypes)

Description: Queries for information about tables that meet conditions in the current catalog. The schemaPattern parameter can be used to specify the schema name or the wildcard represented by %. The tablePattern parameter can be used to specify the table name or the wildcard represented by %. The tableTypes parameter can be used to specify the table type. The table types are MANAGED_TABLE, EXTERNAL_TABLE, VIRTUAL_VIEW, MATERIALIZED_VIEW, and DICTIONARY_TABLE. If these three parameters are empty (using single quotes to substitute the parameters), it returns all catalogs, schemas, table names, table types, and descriptions.

Return type: The row set consisting of the following fields is returned:

• catalog_name
• schema_name
• table_name
• table_type
• description

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

select pg_get_tables('','','');
     pg_get_tables
----------------------
 (catalog,schema_parquet,table1,MANAGED_TABLE,"")
 (catalog,schema_parquet,table2,MANAGED_TABLE,"")
 (catalog,schema_orc,table1,MANAGED_TABLE,"")
(3 row)

select pg_get_tables('%parquet','','MANAGED_TABLE');
     pg_get_tables
----------------------
 (catalog,schema_parquet,table1,MANAGED_TABLE,"")
 (catalog,schema_parquet,table2,MANAGED_TABLE,"")
(2 row)

pg_get_columns(schemaPattern, tableNamePattern, columnNamePattern)

Description: Queries the column information that meets the conditions in the current catalog. The schemaPattern parameter specifies the schema name or the wildcard character %. The tableNamePattern parameter specifies the table name or the wildcard character %. The columnNamePattern parameter specifies the column name or the wildcard character %.

Return type: The row set consisting of the following fields is returned:

• catalog_name
• schema_name
• table_name
• column_name
• origintype
• atttypid
• atttypmod
• typename
• typtype
• attnum
• description
• attnotnull
• description

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

select pg_get_columns('schema_parquet','customer','');
     pg_get_columns
----------------------
 (catalog,schema_parquet,customer,c_custkey,bigint,20,-1,int8,b,1,,f)
 (catalog,schema_parquet,customer,c_name,string,25,-1,text,b,2,,f)
 (catalog,schema_parquet,customer,c_address,string,25,-1,text,b,3,,f)
 (catalog,schema_parquet,customer,c_nationkey,bigint,20,-1,int8,b,4,,f)
 (catalog,schema_parquet,customer,c_phone,"char(15)",1042,19,bpchar,b,5,,f)
 (catalog,schema_parquet,customer,c_acctbal,"decimal(15,2)",1700,983046,numeric,b,6,,f)
 (catalog,schema_parquet,customer,c_mktsegment,"char(10)",1042,14,bpchar,b,7,,f)
 (catalog,schema_parquet,customer,c_comment,string,25,-1,text,b,8,,f)
(8 row)