Overview of Pre-Defined Functions¶
Like any RDBMS, Postgres provides robust set of pre-defined functions to come up with solutions quickly as per the business requirements. There are many functions, but we will see the most common ones here.
- Following are the categories of functions that are more commonly used.
- String Manipulation
- Date Manipulation
- Numeric Functions
- Type Conversion Functions
- CASE and WHEN
- and more
- One can go to the official documentation from Postgres website.
In [1]:
%load_ext sql
In [2]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
In [3]:
%sql SELECT * FROM information_schema.routines LIMIT 10
10 rows affected.
Out[3]:
| specific_catalog | specific_schema | specific_name | routine_catalog | routine_schema | routine_name | routine_type | module_catalog | module_schema | module_name | udt_catalog | udt_schema | udt_name | data_type | character_maximum_length | character_octet_length | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | type_udt_catalog | type_udt_schema | type_udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | routine_body | routine_definition | external_name | external_language | parameter_style | is_deterministic | sql_data_access | is_null_call | sql_path | schema_level_routine | max_dynamic_result_sets | is_user_defined_cast | is_implicitly_invocable | security_type | to_sql_specific_catalog | to_sql_specific_schema | to_sql_specific_name | as_locator | created | last_altered | new_savepoint_level | is_udt_dependent | result_cast_from_data_type | result_cast_as_locator | result_cast_char_max_length | result_cast_char_octet_length | result_cast_char_set_catalog | result_cast_char_set_schema | result_cast_char_set_name | result_cast_collation_catalog | result_cast_collation_schema | result_cast_collation_name | result_cast_numeric_precision | result_cast_numeric_precision_radix | result_cast_numeric_scale | result_cast_datetime_precision | result_cast_interval_type | result_cast_interval_precision | result_cast_type_udt_catalog | result_cast_type_udt_schema | result_cast_type_udt_name | result_cast_scope_catalog | result_cast_scope_schema | result_cast_scope_name | result_cast_maximum_cardinality | result_cast_dtd_identifier |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_retail_db | pg_catalog | boolin_1242 | itversity_retail_db | pg_catalog | boolin | FUNCTION | None | None | None | None | None | None | boolean | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bool | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | boolout_1243 | itversity_retail_db | pg_catalog | boolout | FUNCTION | None | None | None | None | None | None | cstring | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | cstring | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | byteain_1244 | itversity_retail_db | pg_catalog | byteain | FUNCTION | None | None | None | None | None | None | bytea | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bytea | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | byteaout_31 | itversity_retail_db | pg_catalog | byteaout | FUNCTION | None | None | None | None | None | None | cstring | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | cstring | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | charin_1245 | itversity_retail_db | pg_catalog | charin | FUNCTION | None | None | None | None | None | None | “char” | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | char | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | charout_33 | itversity_retail_db | pg_catalog | charout | FUNCTION | None | None | None | None | None | None | cstring | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | cstring | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | namein_34 | itversity_retail_db | pg_catalog | namein | FUNCTION | None | None | None | None | None | None | name | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | name | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | nameout_35 | itversity_retail_db | pg_catalog | nameout | FUNCTION | None | None | None | None | None | None | cstring | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | cstring | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | int2in_38 | itversity_retail_db | pg_catalog | int2in | FUNCTION | None | None | None | None | None | None | smallint | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | int2 | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | int2out_39 | itversity_retail_db | pg_catalog | int2out | FUNCTION | None | None | None | None | None | None | cstring | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | cstring | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
In [4]:
%%sql
SELECT * FROM information_schema.routines
WHERE routine_name ~ 'str'
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 36 rows affected.
Out[4]:
| specific_catalog | specific_schema | specific_name | routine_catalog | routine_schema | routine_name | routine_type | module_catalog | module_schema | module_name | udt_catalog | udt_schema | udt_name | data_type | character_maximum_length | character_octet_length | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | type_udt_catalog | type_udt_schema | type_udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | routine_body | routine_definition | external_name | external_language | parameter_style | is_deterministic | sql_data_access | is_null_call | sql_path | schema_level_routine | max_dynamic_result_sets | is_user_defined_cast | is_implicitly_invocable | security_type | to_sql_specific_catalog | to_sql_specific_schema | to_sql_specific_name | as_locator | created | last_altered | new_savepoint_level | is_udt_dependent | result_cast_from_data_type | result_cast_as_locator | result_cast_char_max_length | result_cast_char_octet_length | result_cast_char_set_catalog | result_cast_char_set_schema | result_cast_char_set_name | result_cast_collation_catalog | result_cast_collation_schema | result_cast_collation_name | result_cast_numeric_precision | result_cast_numeric_precision_radix | result_cast_numeric_scale | result_cast_datetime_precision | result_cast_interval_type | result_cast_interval_precision | result_cast_type_udt_catalog | result_cast_type_udt_schema | result_cast_type_udt_name | result_cast_scope_catalog | result_cast_scope_schema | result_cast_scope_name | result_cast_maximum_cardinality | result_cast_dtd_identifier |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_retail_db | pg_catalog | btvarstrequalimage_5050 | itversity_retail_db | pg_catalog | btvarstrequalimage | FUNCTION | None | None | None | None | None | None | boolean | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bool | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | string_to_array_394 | itversity_retail_db | pg_catalog | string_to_array | FUNCTION | None | None | None | None | None | None | ARRAY | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | _text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | NO | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | array_to_string_395 | itversity_retail_db | pg_catalog | array_to_string | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | NO | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | string_to_array_376 | itversity_retail_db | pg_catalog | string_to_array | FUNCTION | None | None | None | None | None | None | ARRAY | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | _text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | NO | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | array_to_string_384 | itversity_retail_db | pg_catalog | array_to_string | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | NO | MODIFIES | NO | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | strpos_868 | itversity_retail_db | pg_catalog | strpos | FUNCTION | None | None | None | None | None | None | integer | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | int4 | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substr_877 | itversity_retail_db | pg_catalog | substr | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substr_883 | itversity_retail_db | pg_catalog | substr | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substring_936 | itversity_retail_db | pg_catalog | substring | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substring_937 | itversity_retail_db | pg_catalog | substring | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | pg_get_partition_constraintdef_3408 | itversity_retail_db | pg_catalog | pg_get_partition_constraintdef | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | NO | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | pg_get_constraintdef_1387 | itversity_retail_db | pg_catalog | pg_get_constraintdef | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | NO | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | RI_FKey_restrict_del_1648 | itversity_retail_db | pg_catalog | RI_FKey_restrict_del | FUNCTION | None | None | None | None | None | None | trigger | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | trigger | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | NO | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | RI_FKey_restrict_upd_1649 | itversity_retail_db | pg_catalog | RI_FKey_restrict_upd | FUNCTION | None | None | None | None | None | None | trigger | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | trigger | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | NO | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substring_1680 | itversity_retail_db | pg_catalog | substring | FUNCTION | None | None | None | None | None | None | bit | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bit | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substring_1699 | itversity_retail_db | pg_catalog | substring | FUNCTION | None | None | None | None | None | None | bit | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bit | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | string_agg_transfn_3535 | itversity_retail_db | pg_catalog | string_agg_transfn | FUNCTION | None | None | None | None | None | None | internal | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | internal | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | NO | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | string_agg_finalfn_3536 | itversity_retail_db | pg_catalog | string_agg_finalfn | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | NO | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | string_agg_3538 | itversity_retail_db | pg_catalog | string_agg | None | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | NO | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | bytea_string_agg_transfn_3543 | itversity_retail_db | pg_catalog | bytea_string_agg_transfn | FUNCTION | None | None | None | None | None | None | internal | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | internal | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | NO | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | bytea_string_agg_finalfn_3544 | itversity_retail_db | pg_catalog | bytea_string_agg_finalfn | FUNCTION | None | None | None | None | None | None | bytea | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bytea | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | NO | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | string_agg_3545 | itversity_retail_db | pg_catalog | string_agg | None | None | None | None | None | None | None | bytea | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bytea | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | NO | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substring_2012 | itversity_retail_db | pg_catalog | substring | FUNCTION | None | None | None | None | None | None | bytea | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bytea | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substring_2013 | itversity_retail_db | pg_catalog | substring | FUNCTION | None | None | None | None | None | None | bytea | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bytea | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substr_2085 | itversity_retail_db | pg_catalog | substr | FUNCTION | None | None | None | None | None | None | bytea | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bytea | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substr_2086 | itversity_retail_db | pg_catalog | substr | FUNCTION | None | None | None | None | None | None | bytea | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bytea | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substring_2073 | itversity_retail_db | pg_catalog | substring | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | substring_2074 | itversity_retail_db | pg_catalog | substring | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | SQL | None | None | SQL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | cstring_in_2292 | itversity_retail_db | pg_catalog | cstring_in | FUNCTION | None | None | None | None | None | None | cstring | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | cstring | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | cstring_out_2293 | itversity_retail_db | pg_catalog | cstring_out | FUNCTION | None | None | None | None | None | None | cstring | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | cstring | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | cstring_recv_2500 | itversity_retail_db | pg_catalog | cstring_recv | FUNCTION | None | None | None | None | None | None | cstring | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | cstring | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | NO | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | cstring_send_2501 | itversity_retail_db | pg_catalog | cstring_send | FUNCTION | None | None | None | None | None | None | bytea | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | bytea | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | NO | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | pg_get_constraintdef_2508 | itversity_retail_db | pg_catalog | pg_get_constraintdef | FUNCTION | None | None | None | None | None | None | text | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | text | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | NO | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | json_strip_nulls_3261 | itversity_retail_db | pg_catalog | json_strip_nulls | FUNCTION | None | None | None | None | None | None | json | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | json | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | strip_3623 | itversity_retail_db | pg_catalog | strip | FUNCTION | None | None | None | None | None | None | tsvector | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | tsvector | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
| itversity_retail_db | pg_catalog | jsonb_strip_nulls_3262 | itversity_retail_db | pg_catalog | jsonb_strip_nulls | FUNCTION | None | None | None | None | None | None | jsonb | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_retail_db | pg_catalog | jsonb | None | None | None | None | 0 | EXTERNAL | None | None | INTERNAL | GENERAL | YES | MODIFIES | YES | None | YES | 0 | None | None | INVOKER | None | None | None | NO | None | None | None | NO | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None |
In [5]:
%%sql
SELECT substring('Thomas' from 2 for 3)
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[5]:
| substring |
|---|
| hom |
In [6]:
%%sql
SELECT substring('Thomas', 2, 3)
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[6]:
| substring |
|---|
| hom |