Detailed example function - Giswater/giswater_dbmodel GitHub Wiki
-- For function code use any number of 4 DIGITS starting with '9'. It is reserved for you!!!!
-- FUNCTION CODE: 9280
CREATE OR REPLACE FUNCTION "SCHEMA_NAME".user_fct_anl_node_duplicated(p_data json) RETURNS json AS
/*EXAMPLE
SELECT user_fct_anl_node_duplicated($${"client":{"device":4, "infoType":1, "lang":"ES"},
"form":{},"feature":{"tableName":"v_edit_node", "featureType":"NODE", "id":[]},
"data":{"filterFields":{}, "pageInfo":{}, "selectionMode":"wholeSelection",
"parameters":{"exploitation":"1","nodeTolerance":"3.0", "saveOnDatabase":"true"}}}$$)::text
-- For fprocess code (fid) use any number of 3 DIGITS starting with '9'.It is reserved for you!!!!
-- fid: 928
*/
DECLARE
-- Definition of context variables
v_schemaname text;
v_project_type text;
v_version text;
-- Definition of system variables
v_arc_enable_nodes_update boolean;
-- Definition of user variables
v_arc_insert_endpoint boolean;
--Definition of all the variables used in the function
v_id json;
v_selectionmode text;
v_nodetolerance float;
v_saveondatabase boolean;
v_worklayer text;
v_array text;
v_expl_id integer;
rec_arc record;
rec_node record;
--Standard giswater variables used to create return and control exceptional errors
v_version text;
v_result json;
v_result_info json;
v_result_point json;
v_error_context text;
BEGIN
-- Search path - definition of schema in which the function will execute all the processes
SET search_path = "SCHEMA_NAME", public;
v_schemaname = current_schemas(FALSE);
-- Select version values
SELECT giswater INTO v_version FROM sys_version order by 1 desc limit 1; -- version of currently running giswater schema
SELECT project_type INTO v_project_type FROM sys_version order by 1 desc limit 1; -- project type (WS or UD)
-- Getting from system varibles (more than 90)
v_arc_enable_nodes_update := (SELECT value FROM config_param_system WHERE parameter = 'edit_arc_enable_nodes_update ');
-- Getting user variables (more than 190)
v_arc_insert_endpoint := (SELECT value FROM config_param_user WHERE parameter = 'edit_arc_insert_automatic_endpoint' AND cur_user = current_user);
-- Getting input data from the input json - there are two ways of doing it - using '->>' or by json_extract_path_text
v_id := ((p_data ->>'feature')::json->>'id')::json;
v_worklayer := json_extract_path_text (p_data,'feature','tableName')::text;
v_expl_id := json_extract_path_text (p_data,'data','parameters','exploitation')::text;
v_selectionmode := ((p_data ->>'data')::json->>'selectionMode')::text;
v_saveondatabase := (((p_data ->>'data')::json->>'parameters')::json->>'saveOnDatabase')::boolean;
v_nodetolerance := ((p_data ->>'data')::json->>'parameters')::json->>'nodeTolerance';
--Convert json array into quoted text array (needed for this specific function)
select string_agg(quote_literal(a),',') into v_array from json_array_elements_text(v_id) a;
Computing process
/* Remove current values present in anl_* table for the current user and current process fid (defined by user on sys_fprocess table) saving data temporary on anl table allows to save the result that later on will be used to create a temporary layer in qgis.*/
DELETE FROM anl_node WHERE cur_user="current_user"() AND fid=928;
DELETE FROM audit_check_data WHERE cur_user="current_user"() AND fid=928;
-- TIP: Also you can use anl_arc table to work with. Is not this case
DELETE FROM anl_arc WHERE cur_user="current_user"() AND fid=928;
IF v_selectionmode = 'previousSelection' THEN
EXECUTE 'INSERT INTO anl_node (node_id, nodecat_id, state, node_id_aux, nodecat_id_aux, state_aux, expl_id, fid, the_geom)
SELECT * FROM (
SELECT DISTINCT t1.node_id, t1.nodecat_id, t1.state as state1, t2.node_id, t2.nodecat_id, t2.state as state2,
t1.expl_id, 928, t1.the_geom
FROM '||v_worklayer||' AS t1 JOIN '||v_worklayer||' AS t2 ON ST_Dwithin(t1.the_geom, t2.the_geom,('||v_nodetolerance||'))
WHERE t1.node_id != t2.node_id AND t1.node_id IN ('||v_array||')
ORDER BY t1.node_id ) a
WHERE a.state1 > 0 AND a.state2 > 0';
--Insert into table audit_checK_data the information that will be shown to the user in the log tab
INSERT INTO audit_check_data (fid, error_message)
VALUES (928, concat('Looking for nodes duplicated with nodes: ',v_array,'.'));
ELSE
EXECUTE 'INSERT INTO anl_node (node_id, nodecat_id, state, node_id_aux, nodecat_id_aux, state_aux, expl_id, fid, the_geom)
SELECT * FROM (
SELECT DISTINCT t1.node_id, t1.nodecat_id, t1.state as state1, t2.node_id, t2.nodecat_id, t2.state as state2,
t1.expl_id, 928, t1.the_geom
FROM '||v_worklayer||' AS t1 JOIN '||v_worklayer||' AS t2 ON ST_Dwithin(t1.the_geom, t2.the_geom,('||v_nodetolerance||'))
WHERE t1.node_id != t2.node_id AND t1.expl_id ='||v_expl_id||'
ORDER BY t1.node_id ) a
WHERE a.state1 > 0 AND a.state2 > 0';
--Insert into table audit_checK_data the information that will be shown to the user in a log tab
INSERT INTO audit_check_data (fid, error_message)
VALUES (928, 'Looking for nodes duplicated on entire layer.');
END IF;
-- Message to user in special case
IF 'special case' IS TRUE
SELECT gw_fct_getmessage(
--Get results from audit_check_data table in order to show information to the user in a log tab
SELECT array_to_json(array_agg(row_to_json(row))) INTO v_result FROM
(SELECT id, error_message as message FROM audit_check_data WHERE cur_user="current_user"() AND fid=928
ORDER BY id) row;
--Coalesce data in order to avoid passing null values to json
v_result := COALESCE(v_result, '{}');
v_result_info = concat ('{"geometryType":"", "values":',v_result, '}');
v_result_info := COALESCE(v_result_info, '{}');
--Points- get results from anl_node table in order to create a geoJSON to be used on your client
v_result = null;
SELECT jsonb_agg(features.feature) INTO v_result
FROM (
SELECT jsonb_build_object(
'type', 'Feature',
'geometry', ST_AsGeoJSON(the_geom)::jsonb,
'properties', to_jsonb(row) - 'the_geom'
) AS feature FROM
(SELECT id, node_id, nodecat_id, state, node_id_aux,nodecat_id_aux, state_aux, expl_id, descript, fid, the_geom
FROM anl_node WHERE cur_user="current_user"() AND fid=928) row) features;
--TIP: Same for line geometries, not used in this example, to create a geoJSON to be used on your client
v_result = null;
SELECT jsonb_agg(features.feature) INTO v_result
FROM (
SELECT jsonb_build_object(
'type', 'Feature',
'geometry', ST_AsGeoJSON(the_geom)::jsonb,
'properties', to_jsonb(row) - 'the_geom'
) AS feature FROM
(SELECT id, arc_id, arccat_id, state, arc_id_aux, arccat_id_aux, state_aux, expl_id, descript, fid, the_geom
FROM anl_arc WHERE cur_user="current_user"() AND fid=928) row) features;
--Coalesce data in order to avoid passing null values to json
v_result := COALESCE(v_result, '{}');
v_result_point = concat ('{"geometryType":"Point", "features":',v_result, '}');
v_result_point := COALESCE(v_result_point, '{}');
--In this function user may decide if the values are being kept on anl_* table or not
IF v_saveondatabase IS FALSE THEN
--Delete results saved on anl_* table
DELETE FROM anl_node WHERE cur_user="current_user"() AND fid=928;
ELSE
--Set selector in order to see results on views v_anl_*
DELETE FROM selector_audit WHERE fid=928 AND cur_user=current_user;
INSERT INTO selector_audit (fid,cur_user) VALUES (928, current_user);
END IF;
--Return (3.3 & 3.4) - create return
RETURN ('{"status":"Accepted", "message":{"level":1, "text":"Analysis done successfully"}, "version":"'||v_version||'"'||
',"body":{"form":{}'||
',"data":{ "info":'||v_result_info||','||
'"point":'||v_result_point||
'}}'||
'}')::json);
/*Return (version 3.5) - create return using function gw_fct_json_create_return. First argument is a json with defined keys and values determined by developer or captured from result variables. The second one is the id of a function. Function gw_fct_json_create_return allows to create a temporal layer with the results passed to the function with the symbology defined by the user in config_function */
RETURN gw_fct_json_create_return(('{"status":"Accepted", "message":{"level":1, "text":"Analysis done successfully"}, "version":"'||v_version||'"'||
',"body":{"form":{}'||
',"data":{ "info":'||v_result_info||','||
'"point":'||v_result_point||
'}}'||
'}')::json, 9280, null, null);
/*Create exception return that is executed if something goes wrong in the execution of the function. Thanks to this execption the process doesn't break, and it gives user information about the function error.*/
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS v_error_context = PG_EXCEPTION_CONTEXT;
RETURN ('{"status":"Failed","NOSQLERR":' || to_json(SQLERRM) || ',"SQLSTATE":' || to_json(SQLSTATE) ||',"SQLCONTEXT":' || to_json(v_error_context) || '}')::json;
END;
LANGUAGE plpgsql VOLATILE
COST 100;
