| format_type ( type oid, typemod integer ) → text Returns the SQL name for 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. |
| pg_basetype ( regtype ) → regtype Returns the OID of the base type of a domain identified by its type OID. If the argument is the OID of a non-domain type, returns the argument as-is. Returns NULL if the argument is not a valid type OID. If there's a chain of domain dependencies, it will recurse until finding the base type. Assuming CREATE DOMAIN mytext AS text: pg_basetype('mytext'::regtype) → text |
| pg_char_to_encoding ( encoding name ) → integer Converts the supplied encoding name into an integer representing the internal identifier used in some system catalog tables. Returns -1 if an unknown encoding name is provided. |
| pg_encoding_to_char ( encoding integer ) → name Converts the integer used as the internal identifier of an encoding in some system catalog tables into a human-readable string. Returns an empty string if an invalid encoding number is provided. |
| pg_get_catalog_foreign_keys () → setof record ( fktable regclass, fkcols text[], pktable regclass, pkcols text[], is_array boolean, is_opt boolean ) Returns a set of records describing the foreign key relationships that exist within the PostgreSQL system catalogs. The fktable column contains the name of the referencing catalog, and the fkcols column contains the name(s) of the referencing column(s). Similarly, the pktable column contains the name of the referenced catalog, and the pkcols column contains the name(s) of the referenced column(s). If is_array is true, the last referencing column is an array, each of whose elements should match some entry in the referenced catalog. If is_opt is true, the referencing column(s) are allowed to contain zeroes instead of a valid reference. |
| pg_get_constraintdef ( constraint oid [, pretty boolean ] ) → text Reconstructs the creating command for a constraint. (This is a decompiled reconstruction, not the original text of the command.) |
| pg_get_expr ( expr pg_node_tree, relation oid [, pretty boolean ] ) → text Decompiles the internal form of an expression stored in the system catalogs, such as the default value for a column. If the expression might contain Vars, specify the OID of the relation they refer to as the second parameter; if no Vars are expected, passing zero is sufficient. |
| pg_get_functiondef ( func oid ) → text Reconstructs the creating command for a function or procedure. (This is a decompiled reconstruction, not the original text of the command.) The result is a complete CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE statement. |
| pg_get_function_arguments ( func oid ) → text Reconstructs the argument list of a function or procedure, in the form it would need to appear in within CREATE FUNCTION (including default values). |
| pg_get_function_identity_arguments ( func oid ) → text Reconstructs the argument list necessary to identify a function or procedure, in the form it would need to appear in within commands such as ALTER FUNCTION. This form omits default values. |
| pg_get_function_result ( func oid ) → text Reconstructs the RETURNS clause of a function, in the form it would need to appear in within CREATE FUNCTION. Returns NULL for a procedure. |
| pg_get_indexdef ( index oid [, column integer, pretty boolean ] ) → text Reconstructs the creating command for an index. (This is a decompiled reconstruction, not the original text of the command.) If column is supplied and is not zero, only the definition of that column is reconstructed. |
| pg_get_keywords () → setof record ( word text, catcode "char", barelabel boolean, catdesc text, baredesc text ) 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 an unreserved keyword, C for a keyword that can be a column name, T for a keyword that can be a type or function name, or R for a fully reserved keyword. The barelabel column contains true if the keyword can be used as a “bare” column label in SELECT lists, or false if it can only be used after AS. The catdesc column contains a possibly-localized string describing the keyword's category. The baredesc column contains a possibly-localized string describing the keyword's column label status. |
| pg_get_partkeydef ( table oid ) → text Reconstructs the definition of a partitioned table's partition key, in the form it would have in the PARTITION BY clause of CREATE TABLE. (This is a decompiled reconstruction, not the original text of the command.) |
| pg_get_ruledef ( rule oid [, pretty boolean ] ) → text Reconstructs the creating command for a rule. (This is a decompiled reconstruction, not the original text of the command.) |
| pg_get_serial_sequence ( table text, column text ) → text Returns the name of the sequence associated with a column, or NULL if no sequence is associated with the column. If the column is an identity column, the associated sequence is the sequence internally created for that column. For columns created using one of the serial types (serial, smallserial, bigserial), it is the sequence created for that serial column definition. In the latter case, the association can be modified or removed with ALTER SEQUENCE OWNED BY. (This function probably should have been called pg_get_owned_sequence; its current name reflects the fact that it has historically been used with serial-type columns.) The first parameter is a table name with optional schema, and the second parameter is a column name. Because the first parameter potentially contains both schema and table names, it is parsed per usual SQL rules, meaning it is lower-cased by default. The second parameter, being just a column name, is treated literally and so has its case preserved. The result is suitably formatted for passing to the sequence functions (see Section 9.17). A typical use is in reading the current value of the sequence for an identity or serial column, for example: SELECT currval(pg_get_serial_sequence('sometable', 'id')); |
| pg_get_statisticsobjdef ( statobj oid ) → text Reconstructs the creating command for an extended statistics object. (This is a decompiled reconstruction, not the original text of the command.) |
| pg_get_triggerdef ( trigger oid [, pretty boolean ] ) → text Reconstructs the creating command for a trigger. (This is a decompiled reconstruction, not the original text of the command.) |
| pg_get_userbyid ( role oid ) → name Returns a role's name given its OID. |
| pg_get_viewdef ( view oid [, pretty boolean ] ) → text Reconstructs the underlying SELECT command for a view or materialized view. (This is a decompiled reconstruction, not the original text of the command.) |
| pg_get_viewdef ( view oid, wrap_column integer ) → text Reconstructs the underlying SELECT command for a view or materialized view. (This is a decompiled reconstruction, not the original text of the command.) In this form of the function, pretty-printing is always enabled, and long lines are wrapped to try to keep them shorter than the specified number of columns. |
| pg_get_viewdef ( view text [, pretty boolean ] ) → text Reconstructs the underlying SELECT command for a view or materialized view, working from a textual name for the view rather than its OID. (This is deprecated; use the OID variant instead.) |
| pg_index_column_has_property ( index regclass, column integer, property text ) → boolean Tests whether an index column has the named property. Common index column properties are listed in Table 9.75. (Note that extension access methods can define additional property names for their indexes.) NULL is returned if the property name is not known or does not apply to the particular object, or if the OID or column number does not identify a valid object. |
| pg_index_has_property ( index regclass, property text ) → boolean Tests whether an index has the named property. Common index properties are listed in Table 9.76. (Note that extension access methods can define additional property names for their indexes.) NULL is returned if the property name is not known or does not apply to the particular object, or if the OID does not identify a valid object. |
| pg_indexam_has_property ( am oid, property text ) → boolean Tests whether an index access method has the named property. Access method properties are listed in Table 9.77. NULL is returned if the property name is not known or does not apply to the particular object, or if the OID does not identify a valid object. |
| pg_options_to_table ( options_array text[] ) → setof record ( option_name text, option_value text ) Returns the set of storage options represented by a value from pg_class.reloptions or pg_attribute.attoptions. |
| pg_settings_get_flags ( guc text ) → text[] Returns an array of the flags associated with the given GUC, or NULL if it does not exist. The result is an empty array if the GUC exists but there are no flags to show. Only the most useful flags listed in Table 9.78 are exposed. |
| pg_tablespace_databases ( tablespace oid ) → setof oid Returns the set of OIDs of databases that have objects stored in the specified tablespace. If this function returns any rows, the tablespace is not empty and cannot be dropped. To identify the specific objects populating the tablespace, you will need to connect to the database(s) identified by pg_tablespace_databases and query their pg_class catalogs. |
| pg_tablespace_location ( tablespace oid ) → text Returns the file system path that this tablespace is located in. |
| pg_typeof ( "any" ) → regtype 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 Section 8.19); this means that it is the same as an OID for comparison purposes but displays as a type name. pg_typeof(33) → integer |
| COLLATION FOR ( "any" ) → text Returns the name of the collation of the value that is passed to it. The value is quoted and schema-qualified if necessary. If no collation was derived for the argument expression, then NULL is returned. If the argument is not of a collatable data type, then an error is raised. collation for ('foo'::text) → "default" collation for ('foo' COLLATE "de_DE") → "de_DE" |
| to_regclass ( text ) → regclass Translates a textual relation name to its OID. A similar result is obtained by casting the string to type regclass (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. |
| to_regcollation ( text ) → regcollation Translates a textual collation name to its OID. A similar result is obtained by casting the string to type regcollation (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. |
| to_regnamespace ( text ) → regnamespace Translates a textual schema name to its OID. A similar result is obtained by casting the string to type regnamespace (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. |
| to_regoper ( text ) → regoper Translates a textual operator name to its OID. A similar result is obtained by casting the string to type regoper (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found or is ambiguous. |
| to_regoperator ( text ) → regoperator Translates a textual operator name (with parameter types) to its OID. A similar result is obtained by casting the string to type regoperator (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. |
| to_regproc ( text ) → regproc Translates a textual function or procedure name to its OID. A similar result is obtained by casting the string to type regproc (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found or is ambiguous. |
| to_regprocedure ( text ) → regprocedure Translates a textual function or procedure name (with argument types) to its OID. A similar result is obtained by casting the string to type regprocedure (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. |
| to_regrole ( text ) → regrole Translates a textual role name to its OID. A similar result is obtained by casting the string to type regrole (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. |
| to_regtype ( text ) → regtype Parses a string of text, extracts a potential type name from it, and translates that name into a type OID. A syntax error in the string will result in an error; but if the string is a syntactically valid type name that happens not to be found in the catalogs, the result is NULL. A similar result is obtained by casting the string to type regtype (see Section 8.19), except that that will throw error for name not found. |
| to_regtypemod ( text ) → integer Parses a string of text, extracts a potential type name from it, and translates its type modifier, if any. A syntax error in the string will result in an error; but if the string is a syntactically valid type name that happens not to be found in the catalogs, the result is NULL. The result is -1 if no type modifier is present. to_regtypemod can be combined with to_regtype to produce appropriate inputs for format_type, allowing a string representing a type name to be canonicalized. format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)')) → character varying(32) |