Routines

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.

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.

Based on a specially formatted JSON object passed as a parameter, this function will create a new enumeration along with its optional functional types and starting value records.

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.

Applies all of the applicable network rules for a host and returns the governing record for the identified host.

The returned rule is chosen by identifying which rules apply to the host based on the provided Instance related parameters and then limiting the return to the rule with the highest precedence. Currently the precedence is defined as:

1. Disallowed Hosts: Globally disallowed or “banned” hosts are always checked first and no later rule can override the denial. Only removing the host from the syst_disallowed_hosts table can reverse this denial.

2. Global Rules: These are rules applied to all Instances without exception.

3. Instance Rules: Rules defined by Instance Owners and are the most granular rule level available (p_instance_id).

4. Instance Owner Rules: Applied to all Instances owned by the identified Owner (p_instance_owner_id).

5. Global Implied Default Rule: When no explicitly defined network has been found for a host this rule will apply implicitly. The current rule grants access from any host.

Parameters

• p_host_addr :: Required? True; Default: ( No Default )

  The host IP address for which to retrieve a network rule to apply.

• p_instance_id :: Required? False; Default: NULL

  The record id of the Instance that the host is attempting to access.

  Note that NULL is a valid value subject to the conditions in the function description.

• p_instance_owner_id :: Required? False; Default: NULL

  The record id value of the Owner record which owns the Instance.

  Note that NULL is a valid value subject to the conditions in the function description.

General Usage

This function returns the best matching rule for the provided parameters. This means that when p_host_addr is provided but neither of p_instance_id or p_instance_owner_id are provided, the host can only be evaluated against the Disallowed Hosts and the Global Network Rules which isn’t sufficient for a complete validation of a host’s access to an Instance; such incomplete checks can be useful to avoid more expensive authentication processes later if the host is just going to be denied access due to global network rules.

Providing only p_instance_owner_id will include the Global and Owner defined rules, but not the Instance specific rules. If p_instance_id is provided that is sufficient that is sufficient for testing all the applicable rules since the Instance Owner ID can be derived using just p_instance_id parameter.

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.

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.

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.

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 regclasses 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.

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.

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

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.

Encodes integers into a Base36 representation.

Parameters

• p_value :: Required? True; Default: ( No Default )

  A big integer value to encode as a Base36.

A constraint trigger function to provide foreign key like validation of columns which reference syst_enum_items.

Trigger Function Details:

• Supported Timing: AFTER

• Supported Operations: INSERT, UPDATE

Parameters

• tg_argv[0] :: Required? True; Default: ( No Default )

  The name of the enumeration to validate against.

  This value will be the ms_syst_data.syst_enums.internal_name value which identified the enumeration.

• tg_argv[1] :: Required? True; Default: ( No Default )

  The name of the foreign key column in the host table which references ms_syst_data.syst_enum_items records.

General Usage

This relationship requires the additional check that only values from the desired enumeration are used in assigning to records.

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.

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.

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.

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.

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.

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.

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 RAISEd 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.