Register new PLpgSQL function - Giswater/giswater_dbmodel GitHub Wiki

Any user can create a new functionality, that may be executed from the toolbox of Giswater. Here you can find a few guidelines that will help you bring your own tool to life.

The database functions are written in PL/SQL, which combines SQL with procedural features of programming language. In order to make the function work its necessary to follow the defined structure. In order to use toolbox it’s important to use json input and output data type. All the parameters passed to the function and set by user can be defined in a config_toolbox table, which we will analyze later on. Let’s see everything in the example – a simple function that finds duplicated nodes from the entire layer or users selection. It takes node tolerance value, exploitation and saving on database as input parameters. You will find the function with comments in the section Example function.

REGISTER FUNCTION ON SYS TABLES

First let’s define the function in sys_function table. Its’s important to remember that all processes and functions have an id that starts with 9. For function numeration we use 4 digits, so the user can assign values from 9000 till 9999. Same rule applies to the definition of processes, where we use 3 digits, so user may use numbers from 900 till 999. Fid is being used for saving temporary data on anl_* tables or audit_check_data table.

INSERT INTO sys_function(id, function_name, project_type, function_type, input_params, return_type, descript, sys_role)
VALUES(9280, 'user_fct_anl_node_duplicated', 'utils', 'function', 'json', 'json', 'Detect duplicated nodes', 'role_edit');

Column name	Description
id	Id of the function starting with 9 - 9XXX
function_name	Name of the function
project_type	Type of the project: ‘ws’,’ud’,’utils’
function_type	Type of the function: ‘function’,’trigger function’
input_params	Type of input parameters. Recommended: json
return_type	Type of output. Recommended: json
descript	Function description
sys_role	Role permissions: ‘role_basic’,‘role_om’, ‘role_edit’, ‘role_master’, ‘role_admin’

INSERT INTO sys_fprocess (fid, fprocess_name, project_type)
VALUES (928,'Detect duplicated nodes', 'utils');

Column name	Description
fid	Id of the process starting with 9 - 9XX
fprocess_name	Name of the process
project_type	Type of the project: ‘ws’,’ud’,’utils’