-
Notifications
You must be signed in to change notification settings - Fork 11
Register new PLpgSQL function
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’ |