maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_ecm_femsa(timestamp with time zone, timestamp with time zone)
Parameters
Name
Type
Mode
start_date
timestamp with time zone
IN
end_date
timestamp with time zone
IN
Definition
DECLARE buses varchar[]; bus varchar; jpod_keys varchar[]; jpod_key varchar; BEGIN SET timezone = 'America/Mexico_city'; buses := '{j1708,j1939}'; jpod_keys := '{Odometer,HiResOdometer,TotalFuel,EngineSpeed,RoadSpeed,BatteryVoltage,SwitchedBatVoltage,TotalIdleFuel,TotalIdleHours,TotalEngineHours,EngineCoolantTemp,EngineOilTemp}'; FOREACH bus IN ARRAY buses LOOP FOREACH jpod_key IN ARRAY jpod_keys LOOP EXECUTE format('INSERT INTO tmp.ecm_femsa WITH q AS (SELECT d.serialnumber,u.alias econum,e.senddate,%L bus,%L jpod_key,coalesce(nullif((e.otros->%L),'''')::float,0)::numeric as val FROM event_ecm_201511 e JOIN device d ON d.iddevice = e.iddevice JOIN tmp.femsa_serials s ON s.s = d.serialnumber JOIN unit u ON u.idunit = d.idunit WHERE e.senddate BETWEEN %L and %L ) SELECT serialnumber,econum,senddate,bus,jpod_key,val FROM q ;',bus,jpod_key,bus||'_'||jpod_key,start_date,end_date); END LOOP; END LOOP; RETURN true; END;