Create function
Unique function behavior in postgresql:Functions with same name can exist along with a proc or function in the same schema, as long as they use different argument list - function overloading
We create a test function to be called from trigger:
CREATE FUNCTION FUNC_wbaclustondml() RETURNS trigger AS $$
BEGIN
execute format('cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey');
analyze verbose pgtst_tbl1_random;
END;
$$ LANGUAGE plpgsql;
To list all the functions created in the current database/schema:
select * from information_schema.routines where routine_type = 'FUNCTION' and specific_schema='pgtst_schema';
Actual output:
pgtst_db=> \c
You are now connected to database "pgtst_db" as user "pgtst_usr".
pgtst_db=>
pgtst_db=> select * from information_schema.routines where routine_type = 'FUNCTION';
-[ RECORD 1 ]-----------------------+----------------------------------------------------------------------------------
specific_catalog | pgtst_db
specific_schema | pgtst_schema
specific_name | func_wbaclustondml_24611
routine_catalog | pgtst_db
routine_schema | pgtst_schema
routine_name | func_wbaclustondml
routine_type | FUNCTION
module_catalog |
module_schema |
module_name |
udt_catalog |
udt_schema |
udt_name |
data_type | trigger
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 | pgtst_db
type_udt_schema | pg_catalog
type_udt_name | trigger
scope_catalog |
scope_schema |
scope_name |
maximum_cardinality |
dtd_identifier | 0
routine_body | EXTERNAL
routine_definition | +
| BEGIN +
| execute format('cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey');+
| analyze verbose pgtst_tbl1_random; +
| END; +
|
external_name |
external_language | PLPGSQL
parameter_style | GENERAL
is_deterministic | NO
sql_data_access | MODIFIES
is_null_call | NO
sql_path |
schema_level_routine | YES
max_dynamic_result_sets | 0
is_user_defined_cast |
is_implicitly_invocable |
security_type | INVOKER
to_sql_specific_catalog |
to_sql_specific_schema |
to_sql_specific_name |
as_locator | NO
created |
last_altered |
new_savepoint_level |
is_udt_dependent | NO
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 |
pgtst_db=>
Thanks
No comments:
Post a Comment