It helps to modify a function sql in postgresql

0

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.

    
asked by cratus666jose 03.10.2018 в 18:09
source

0 answers