Anonymous

CREATE OR REPLACE FUNCTION device_daily_statistics (v_device_id int4, v_poll_date date) 
RETURNS text AS
$$
DECLARE
	f accounts_devices_polldata%rowtype;
	l accounts_devices_polldata%rowtype;
	v_traffic_in int4;
	v_traffic_out int4;
	v_avg_ds_pwr int4;
	v_avg_ds_snr int4;
	v_avg_tx_pwr int4;
BEGIN
	SELECT * INTO f FROM accounts_devices_polldata 
	WHERE device_id = v_device_id AND cmts_status = 6 AND cast(poll_time as date) = v_poll_date 
	AND poll_time = (select min(poll_time) from accounts_devices_polldata where device_id = v_device_id AND cast(poll_time as date) = v_poll_date);

	SELECT * INTO l FROM accounts_devices_polldata 
	WHERE device_id = v_device_id AND cmts_status = 6
	AND cast(poll_time as date) = v_poll_date 
	AND poll_time = (select max(poll_time) from accounts_devices_polldata where device_id = v_device_id AND cast(poll_time as date) = v_poll_date);

	v_traffic_in := (l.traffic_in - f.traffic_in);
	v_traffic_out := (l.traffic_out - f.traffic_out);
	v_avg_ds_pwr := (select cast(avg(ds_power) as int4) from accounts_devices_polldata where device_id = v_device_id AND cmts_status = 6 AND cast(poll_time as date) = v_poll_date group by device_id);
	v_avg_ds_snr := (select cast(avg(ds_snr) as int4) from accounts_devices_polldata where device_id = v_device_id AND cmts_status = 6 AND cast(poll_time as date) = v_poll_date group by device_id);
	v_avg_tx_pwr := (select cast(avg(tx_power) as int4) from accounts_devices_polldata where device_id = v_device_id AND cmts_status = 6 AND cast(poll_time as date) = v_poll_date group by device_id);

	return 'Traffic IN: ' || cast(v_traffic_in as text) || ' Traffic OUT: ' || cast(v_traffic_out as text) || ' DS Power: ' || cast(v_avg_ds_pwr as text) || ' DS SNR: ' || cast(v_avg_ds_snr as text) || ' TX Power: ' || cast(v_avg_tx_pwr as text);
END
$$
LANGUAGE 'plpgsql';