mscmp_syst_settings.ms_syst

Routines

Name	Type	Language	Deterministic	Return Type	Security Restriction	Comments
generate_comments_table_column(p_table_schema text, p_table_name text, p_comments_config ms_syst_priv.comments_config_table_column)	FUNCTION	plpgsql	false	void	INVOKER	Generates table column comments in a standardized format. The comments themselves are defined using a configuration type containing the column documentation and comment related configurations. Parameters `p_table_schema` :: Required? True; Default: ( No Default ) The database schema where the column is defined. `p_table_name` :: Required? True; Default: ( No Default ) The database table where the column is defined. `p_comments_config` :: Required? True; Default: ( No Default ) A value of type `ms_syst_priv.comments_config_table_column` which describes the required and optional attributes for generating the column’s comments. See the comments for that database type for detailed information.
generate_comments_apiview_column(p_view_config ms_syst_priv.comments_config_apiview, p_column_config ms_syst_priv.comments_config_apiview_column)	FUNCTION	plpgsql	false	void	INVOKER	Generates API View Column comments based on the passed comment configurations. Parameters `p_view_config` :: Required? True; Default: ( No Default ) A value of type `ms_syst_priv.comments_config_apiview` which contains view level configuration information such as identification of the view, associated Data Table identification, available record modes (system defined & user defined), and similar concerns which can influence column comment generation. This value is required, though only the following fields are used: `view_schema` - (required) `view_name` - (required) `table_schema` - (optional) `table_name` - (optional) `user_records` - (optional) `user_insert` - (optional) `user_select` - (optional) `user_update` - (optional) `syst_records` - (optional) `syst_select` - (optional) `syst_update` - (optional) Any fields passed as `NULL` will assume their default values. See the database type documentation for more information, including to find any defined field level default values. `p_column_config` :: Required? True; Default: ( No Default ) A value of type `ms_syst_priv.comments_config_apiview_column` which configures the comments to generate for the identified API View Column. See the database type documenation for more information. General Usage While optional, if the targeted API View Column is identified as being closely related to an underlying Data Table Column, this function will attempt to extract descriptive texts from the Data Table Column comments so that these descriptions don’t need to be duplicated manually for the API View; this behavior may be overridden in the passed comment configuration.
nonstandard_decode(p_base integer, p_tokens text, p_value text)	FUNCTION	plpgsql	true	bigint	INVOKER	Performs a decode to decimal operation, similar to the standard decode function, but for non-standard decoding schemes such as Base32 or Base36. Parameters `p_base` :: Required? True; Default: ( No Default ) The number base that the value has been encoded in. For example, Base36 the `p_base` value is `36`. `p_tokens` :: Required? True; Default: ( No Default ) The tokens used in representing the numbering scheme. The count of characters passed in this parameter should match the `p_base` parameter. `p_value` :: Required? True; Default: ( No Default ) The encoded value to convert to decimal.
generate_comments_apiview_common_columns(p_view_config ms_syst_priv.comments_config_apiview)	FUNCTION	plpgsql	false	void	INVOKER	Provides boilerplate API View Column comment configurations and generates the comments for columns which appear in many places across the applications and where standardized descriptions are likely to apply. Parameters `p_view_config` :: Required? True; Default: ( No Default ) A required value of type `ms_syst_priv.comments_config_apiview`. This value provides the basic comments configuration data used to determine to which API View to apply common column comments, find which columns may be present, etc. Only the fields of the value below are used by this function: `view_schema` - (required) `view_name` - (required) `table_schema` - (required) `table_name` - (required) `user_records` - (optional) `user_insert` - (optional) `user_select` - (optional) `user_update` - (optional) `syst_records` - (optional) `syst_select` - (optional) `syst_update` - (optional) Any fields passed as `NULL` will assume their default values. See the database type documentation for more information, including to find any defined field level default values. Failing to provide a `table_schema` and `table_name` will not raise an exception, but may produce unsatisfactory results. In cases where the API View is not strongly associated with a Data Table, common columns should be documentend manually. General Usage This function expects that a there is a closely associated Data Table defined. The Data Table columns provide the descriptive texts for the related API View columns. If the API View is not closely associated with an underlying Data Table, common columns should be configured manually as regular API View Columns and passed directly to either `ms_syst_priv.generate_comments_apiview` or `ms_syst_priv.generate_comments_apiview_column` as appropriate.
decode_base36(p_value text)	FUNCTION	plpgsql	true	bigint	INVOKER	Decodes integers represented in Base36 notation back to decimal form. Parameters `p_value` :: Required? True; Default: ( No Default ) The Base36 value to decode to decimal.
generate_comments_copy_function(p_source_schema text, p_source_name text, p_target_schema text, p_target_name text, p_supplemental text DEFAULT NULL::text)	FUNCTION	plpgsql	false	void	INVOKER	Copies the comments of a source function to be the comments of a different target function. Parameters `p_source_schema` :: Required? True; Default: ( No Default ) The name of the schema which hosts the function whose comments are to be copied. `p_source_name` :: Required? True; Default: ( No Default ) The function name from which comments will be copied. `p_target_schema` :: Required? True; Default: ( No Default ) The name of the schema which hosts the function which will receive the copied comments. `p_target_name` :: Required? True; Default: ( No Default ) The name of the function to which the copied comments will be applied. `p_supplemental` :: Required? False; Default: ( No Default ) An optional text which can be added to the copied comments in a special “Supplemental” section. This allows the target function to have additional notes if appropriate. General Usage The use case for this function is many Public API functions are just wrappers exposing functions defined in the private logic. In these cases the private function logic will generally apply to the Public API wrapper and the private function can serve as a single source of the documentation. Note that this function current doesn’t work with overloaded functions.
is_parent_record_referenced(p_table_schema text, p_table_name text, p_parent_record_id uuid, p_excluded_relations regclass[] DEFAULT '{}'::regclass[])	FUNCTION	plpgsql	false	boolean	INVOKER	Tests if a specific parent record is referenced in a foreign key relationship. Parameters `p_table_schema` :: Required? True; Default: ( No Default ) The name of the schema which hosts the parent table. `p_table_name` :: Required? True; Default: ( No Default ) The name of the parent table. `p_parent_record_id` :: Required? True; Default: ( No Default ) The ID value of the parent record which will be searched for in the child tables. `p_excluded_relations` :: Required? False; Default: No excluded relations An optional array of child table `regclass`es which should not be searched. General Usage The parent table is identified by the `p_table_schema` and `p_table_name` parameters and the specific record is identified using the `p_parent_record_id` parameter. The `p_parent_record_id` is expected to be the `id` column value of the parent relation. On execution, the function will then look up all relations (children) with foreign key references to the parent table and its `id` column and test if the `p_parent_record_id` value exists in the child relation. Once a child relation with a reference is found, then the function returns `TRUE` and the search stops. If no child relations have a reference to the given parent record, then the function returns false. There are several assumptions to be aware of when making use of this function. The function assumes that any foreign key reference to the parent relation is a simple, single column relationship to the parent relation’s `id` column; an assumption is made that constraint names are unique across the application, but there is no guarantee from PostgreSQL that this is true; and that by using this function the developer is aware that when there are either many child relations and/or the child relations contain many records, this function may not perform well, especially in cases where there are no references to the given parent record. Finally, if any of the parameter values are `NULL`, the function will return `NULL`.
generate_comments_table_common_columns(p_table_schema text, p_table_name text)	FUNCTION	plpgsql	false	void	INVOKER	Provides boilerplate column comment configurations for columns which are common to many columns and applies these comment configurations to any table columns which are found to be in the set of common columns. Parameters `p_table_schema` :: Required? True; Default: ( No Default ) The name of the schema which hosts the table. `p_table_name` :: Required? True; Default: ( No Default ) The name of the table for which common column comments should be added. General Usage Note that if there are customizations or overrides desired when documenting these common columns for a given table, such overrides should appear in the columns list passed to `ms_syst_priv.generate_comments_table`, directly by calling `ms_syst_priv.generate_comments_table_column`, or by simply commenting on the desired column directly.
trig_b_iu_set_diagnostic_columns()	FUNCTION	plpgsql	false	trigger	INVOKER	Automatically maintains the common table diagnostic columns whenever data is inserted or updated. Trigger Function Details: Supported Timing: `BEFORE` Supported Operations: `INSERT`, `UPDATE` General Usage For `UPDATE` transactions, the trigger will determine if there are ‘real data changes’, meaning any fields other than the common diagnostic columns being changed by the transaction. If not, only the `diag_update_count` column will be updated. To use this trigger, the targeted table must have the following columns / types defined: `diag_timestamp_created` / `timestamptz` `diag_role_created` / `text` `diag_timestamp_modified` / `timestamptz` `diag_wallclock_modified` / `timestamptz` `diag_role_modified` / `text` `diag_row_version` / `bigint` `diag_update_count` / `bigint`
generate_comments_table(p_comments_config ms_syst_priv.comments_config_table)	FUNCTION	plpgsql	false	void	INVOKER	Generates table comments, and optionally associated column comments, in a standardized format. Parameters `p_comments_config` :: Required? True; Default: ( No Default ) A value of type `ms_syst_priv.comments_config_table` which describes the required and optional attributes for generating the column’s comments. See the comments for that database type for detailed information. General Usage The comments themselves are defined using an object of type `ms_syst_priv.comment_configs_table` which in turn is used as the parameter of this function.
encode_base36(p_value bigint)	FUNCTION	sql	true	text	INVOKER	Encodes integers into a Base36 representation. Parameters `p_value` :: Required? True; Default: ( No Default ) A big integer value to encode as a Base36.
generate_comments_apiview(p_comments_config ms_syst_priv.comments_config_apiview)	FUNCTION	plpgsql	false	void	INVOKER	Generates API View comments as well as comments for any requested columns. API Views are typically closely associated with specific Data Tables. While this function does not require Data Table to API View parity of columns, types, etc. it does assume that a substantial amount of Data Table to API View parity exists. Given this, the descriptive texts of the API View and its columns are extracted from the Data Table comments and applied to the API View. Additional information that is specific for the API View is then added using the configurations passed in the `p_comments_config` parameter. Parameters `p_comments_config` :: Required? True; Default: ( No Default ) A `ms_syst_priv.comments_config_apiview` value which provides the configuration and texts from which to generate the API View documentation. See the documentation of that database type for more information.
encode_base32(p_value bigint)	FUNCTION	sql	true	text	INVOKER	Encodes a big integer value into Base32 representation. The representation here is that designed by Douglas Crockford (https://www.crockford.com/base32.html). Parameters `p_value` :: Required? True; Default: ( No Default ) The value to encode as a Crockford Base32 value.
decode_base32(p_value text)	FUNCTION	plpgsql	true	bigint	INVOKER	Decodes integers represented in Base32. The representation here is that designed by Douglas Crockford (https://www.crockford.com/base32.html). Parameters `p_value` :: Required? True; Default: ( No Default ) The Base32 encoded value to decode back to a decimal representation.
get_random_string(p_length integer, p_tokens text DEFAULT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'::text)	FUNCTION	sql	false	text	INVOKER	Returns a random text string, by default consisting of alpha-numeric symbols, of the requested length. An arbitrary set of characters from which to draw the random string may be provided my the caller. Parameters `p_length` :: Required? True; Default: ( No Default ) The number of random characters to return. `p_tokens` :: Required? False; Default: `0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ` An option alternate set of characters to use in the generation.
generate_comments_function(p_comments_config ms_syst_priv.comments_config_function)	FUNCTION	plpgsql	false	void	INVOKER	Generates comments for functions, procedures, and trigger functions. Parameters `p_comments_config` :: Required? True; Default: ( No Default ) A configuration object of type `ms_syst_priv.comments_config_function` which is used in automatically generating the appropriately formatted function comments. General Usage If the default value of a documented function is longer than ~40 characters, it may be better to set the `default_value` to refer to the “General Usage” section and then explain the default value there. This due to line-length issues in the resulting formatted comments.
nonstandard_encode(p_base integer, p_tokens text, p_value bigint)	FUNCTION	plpgsql	true	text	INVOKER	Performs an encode operation, similar to the standard encode function, but for non-standard encoding schemes such as Base32 or Base36. Parameters `p_base` :: Required? True; Default: ( No Default ) The number base that the encoding system is expecting. For example, Base36 the `p_base` value is `36`. `p_tokens` :: Required? True; Default: ( No Default ) The tokens to use in representing the numbering scheme. The count of characters passed in this parameter should match the `p_base` parameter. `p_value` :: Required? True; Default: ( No Default ) The decimal value to encode in the requested base.
get_exception_details(p_proc_schema text, p_proc_name text, p_exception_name text, p_errcode text, p_param_data jsonb, p_context_data jsonb)	FUNCTION	sql	false	text	INVOKER	Returns exception details based on the passed parameters represented as a pretty-printed JSON object. The returned value is intended to standardize the details related to `RAISE`d exceptions and be suitable for use in setting the `RAISE DETAILS` variable. Parameters `p_proc_schema` :: Required? True; Default: ( No Default ) The schema name hosting the function or store procedure which raised the exception. `p_proc_name` :: Required? True; Default: ( No Default ) The name of the process which raised the exception. `p_exception_name` :: Required? True; Default: ( No Default ) A standard name for the exception raised. `p_errcode` :: Required? True; Default: ( No Default ) Error code complying with the PostgreSQL standard error codes (https://www.postgresql.org/docs/current/errcodes-appendix.html). Typically this will be a compatible error code made outside of already designated error codes. `p_param_data` :: Required? False; Default: ( No Default ) A `jsonb` object where the keys are relevant parameters. `p_context_data` :: Required? False; Default: ( No Default ) A `jsonb` object encapsulating relevant data which might help in interpreting the exception, if such data exists.