My store procedure does not return anything stored in json

2

I hope and they are well, I wanted to ask them what is wrong with my store procedure, what happens is that a whole query I am storing in a Json, and the problem is that the SP does not return anything, look at this It's my code:

-- FUNCTION: public.sp_get_combustible_report(integer, integer, character varying, character varying, character varying)

-- DROP FUNCTION public.sp_get_combustible_report(integer, integer, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION public.sp_get_combustible_report(
    in_origin_adm integer,
    in_type integer,
    in_date1 character varying,
    in_date2 character varying,
    cves character varying)
    RETURNS TABLE(var_json jsonb) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$

DECLARE
var_jsonresp jsonb:='[]';
JSON_ARRAY_CVES JSON := $5::json;

BEGIN
select COALESCE(array_to_json(array_agg(row_to_json(det))),'[]') from 
    (select fuel_consum_ref as numTrasaccion,
        vehicle_name as numEconomico,
        num_gas_station as numEstacion,
        gas_station_bomb as bomba,
        fuel_odometer as metro,
        desc_fuel_type as producto,
        fuel_consum_qty as consumoPesos,
        to_char (fuel_consum_total,'LFM9,999,999.00') as consumolitros,
        to_char (date_fuel_consum,'dd-MM-yyyy')::text as fechaFacturacion,
        cost_center as centroCosto
        --custom_field_data as numtarjeta
        from tab_fuel_consum tfc
        join cat_vehicle cv on tfc.cve_vehicle = cv.cve_vehicle  
        join cat_gas_station cgs on --tfc.origin_adm = cgs.origin_adm 
        --and 
        tfc.cve_gas_station = cgs.cve_gas_station
        join cat_fuel_type cft on tfc.cve_fuel_type = cft.cve_fuel_type and tfc.cve_fuel= cft.cve_fuel
        join tab_fuel_custom_field tfcf on cv.cve_vehicle = tfcf.cve_vehicle 
        and tfcf.origin_adm = tfc.origin_adm 
        and tfcf.cve_vehicle = tfc.cve_vehicle
        and tfcf.cve_fuel_consum = tfc.cve_fuel_consum 
        where tfc.origin_adm  in(SELECT spc.out_origin_adm FROM sp_get_company_childs(1,1) AS spc) 
        and tfc.last_update BETWEEN $3::timestamp without time zone AND 
                                   to_timestamp($4,'YYYY-MM-DD HH24:MI:SS')::timestamp without time zone
        and cv.cve_vehicle::TEXT IN (select * from json_array_elements_text(JSON_ARRAY_CVES))
        and tfcf.custom_field_name='noTarjeta'
        and tfc.status='1'
        )det into var_jsonresp; 

RETURN QUERY SELECT var_jsonresp; 
    END;


$BODY$;

ALTER FUNCTION public.sp_get_combustible_report(integer, integer, character varying, character varying, character varying)
    OWNER TO postgres;

With this I execute the SP: select * from sp_get_combust_report (1, 2, '01 -01-2018 ', '01 -10-2018', '[3652,4150]');

And in the result I returned empty, I hope and can help me with this problem please, I remain attentive to your comments, greetings.

    
asked by cratus666jose 26.09.2018 в 18:32
source

0 answers