good day, I have an SP in postgresql and the problem here is to put another case where that case receives as a parameter a 2 that eliminates, what happens is that I do not know much plsql and then I do not know how it goes:
This is the function:
-- FUNCTION: public.sp_insert_update_route_assignment(text, integer)
-- DROP FUNCTION public.sp_insert_update_route_assignment(text, integer);
CREATE OR REPLACE FUNCTION public.sp_insert_update_route_assignment(
in_json text,
opt integer)
RETURNS TABLE(out_var_boolean boolean)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
jsonData json;
var_boolean boolean := false;
var_cons int;
rows_count int;
listripulante json;
var_origin integer;
var_counts integer;
var_incrementoCve integer;
BEGIN
jsonData := in_json:: json;
case opt when 1 then
IF((jsonData->>'delete_rows'):: integer = 1) THEN
--RAISE NOTICE '%', jsonData->>'delete_rows' ;
UPDATE cat_vehicle SET cve_delivery_route=null WHERE origin_adm IN (SELECT out_origin_adm FROM sp_get_company_childs((jsonData->>'origin'):: integer,1)) AND cve_vehicle=(jsonData->>'cve_vehicle_delete'):: integer;
DELETE FROM cat_vehicle_driver WHERE origin_adm IN (SELECT out_origin_adm FROM sp_get_company_childs((jsonData->>'origin'):: integer,1)) AND cve_vehicle=(jsonData->>'cve_vehicle_delete'):: integer;
END IF;
select count (*) from cat_vehicle where cve_delivery_route=(jsonData->>'cve_route'):: integer into var_counts;
if(var_counts >0) then
update cat_vehicle set cve_delivery_route= null;
end if;
UPDATE cat_vehicle SET cve_delivery_route=(jsonData->>'cve_route'):: integer
WHERE origin_adm IN (SELECT out_origin_adm FROM sp_get_company_childs((jsonData->>'origin'):: integer,1)) AND cve_vehicle=(jsonData ->> 'cve_vehicle'):: integer;
get diagnostics rows_count = row_count;
IF(rows_count > 0) THEN
var_boolean:=true;
ELSE
var_boolean:=false;
END IF;
IF(var_boolean = true) THEN
--Un validador aqui, si ya hay registros, solo inserte
--IF (jsonData->>'cve_employee_assignment' )
--IF opt=2 THEN
DELETE FROM tab_delivery_assignment WHERE cve_delivery_route =(jsonData->>'cve_employee_assignment'):: integer ;
end if;
FOR listripulante IN SELECT * FROM json_array_elements((jsonData ->> 'listripulante'):: json)
LOOP
select max(cve_employee_assignment)+1 from tab_delivery_assignment into var_incrementoCve;
if(var_incrementoCve is Null or var_incrementoCve=0 )then
var_incrementoCve=1;
end if;
INSERT INTO tab_delivery_assignment VALUES(
(jsonData->>'cve_route'):: integer ,
var_incrementoCve,
(listripulante->>'cve_employee_name'):: character varying
);
get diagnostics rows_count = row_count;
IF(rows_count = 0) THEN
var_boolean:=false;
END IF;
END LOOP;
END IF;
return QUERY SELECT var_boolean;
end case;
END;
$BODY$;
ALTER FUNCTION public.sp_insert_update_route_assignment(text, integer)
OWNER TO postgres;
I hope and you can give me to understand, I keep an eye on any comments, greetings.