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.