Database Object Position Functions

pg_relation_filenode(relation regclass)

Description: Specifies the ID of a filenode with the specified relationship.

Return type: oid

Note: pg_relation_filenode receives the OID or name of a table, an index, a sequence, or a compressed table, and returns the ID of filenode allocated to it. filenode is the basic component of the file name used by the relationship. For most tables, the result is the same as that of pg_class.relfilenode. For the specified system directory, relfilenode is 0 and this function must be used to obtain the correct value. If a relationship that is not stored is passed, such as a view, this function returns a null value.

pg_relation_filepath(relation regclass)

Description: Specifies the name of a file path with the specified relationship. It can only be used for non-segment page relationships.

Return type: text

Description: pg_relation_filepath is similar to pg_relation_filenode, except that pg_relation_filepath returns the whole file path name for the relationship (relative to the data directory PGDATA of the database cluster).

For segment-page relationships, you are advised to use functions or views. For example:

SELECT e.*, f.file_name
FROM gs_seg_extents e, gs_seg_datafiles f
WHERE e.tablespace_name = f.tablespace_name AND e.bucketnode = f.bucketnode ADN e.file_id = f.file_id ADN e.forknum = f.forknum;

get_large_table_name(relfile_node text, threshold_size_gb int8)

Description: Queries whether the table size (in GB) exceeds the threshold (threshold_size_gb) based on the table file code (relfile_node). If the size exceeds the threshold, the schema name and table name (in schemaname.tablename format) are returned, otherwise, null is returned.

Return type: text

pg_filenode_relation(tablespacename, relname)

Description: Obtains the table names corresponding to the tablespace and relfilenode.

Return type: regclass

pg_partition_filenode(partition_oid)

Description: Obtains filenode corresponding to the OID lock of a specified partitioned table.

Return type: oid

pg_partition_filepath(partition_oid)

Description: Specifies the file path name of a specified partition. It can only be used for non-segment page relationships.

Return type: text

Note: For segment-page relationships, you are advised to use functions or views. For example:

SELECT e.*, f.file_name
FROM gs_seg_extents e, gs_seg_datafiles f
WHERE e.tablespace_name = f.tablespace_name ADN e.bucketnode = f.bucketnode ADN e.file_id = f.file_id ADN e.forknum = f.forknum;