task.sql

nesC写的heed算法

create or replace function humidity(int /* raw humidity */) returns real
	as 'select (-4 + 0.0405 * $1 + -2.8e-6 * $1 * $1)::real as result;'
	language sql;

create or replace function humid_temp(int /* raw temperature */) returns real
	as 'select (-38.4 + 0.0098 * $1)::real as result;'
	language sql;

create or replace function humid_adj(int /* raw humidity */, int /* raw temperature */) returns real
	as 'select ((humid_temp($2) - 25) * (0.01 + 0.00008 * $1) + humidity($1))::real as result;'
	language sql;

CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAGE c;


CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;

create or replace function photo(int /* channel 0 */, int /* channel 1 */) returns real
	as 'declare
			s0 int;
			cc0 int;
			s1 int;
			cc1 int;
			adccount0 int;
			adccount1 int;
			lux real := 0;
		begin
			s0 := $1 & 15;
			cc0 := (($1 & 127) >> 4) & 7;
			s1 := $2 & 15;
			cc1 := (($2 & 127) >> 4) & 7;
			-- check if we are railed
			if s0 = 15 and s1 = 15 and cc0 = 7 and cc1 = 7
			then return null;
			end if;

			adccount0 := (16.5 * (2^cc0 - 1))::int + (s0 * 2^cc0)::int;
			adccount1 := (16.5 * (2^cc1 - 1))::int + (s1 * 2^cc1)::int;

			if adccount0 = 0 then return null; end if;
			lux := adccount0 * 0.46 * exp(-3.13 * (adccount1 / adccount0));
			return lux;
		end;'
	language 'plpgsql';

create or replace function thermopile(int /* raw thermopile */) returns real
	as 'select ((($1 >> 4) * (120 - (-20))) / (2^12 - 1) + (-20))::real as result;'
	language sql;

create or replace function thermistor(int /* raw temperature */) returns real
	as 'select ((($1 >> 4) * (85 - (-20))) / (2^12 - 1) + (-20))::real as result;'
	language sql;

create or replace function voltage(int /* raw voltage */) returns real
	as 'select (case when $1 = 0 then 0 else 0.58 * 1024 / $1 end)::real as result;'
	language sql;

create or replace function hamamatsu_current(int /* raw hamamatsu */, int /* raw voltage */) returns real
	as 'select (($1 * voltage($2) / 1024.0) / 5.0)::real as result;'
	language sql;

create or replace function hamamatsu(int /* raw hamamatsu */, int /* raw voltage */) returns real
	as 'select (hamamatsu_current($1, $2) * 1e6)::real as result;'
	language sql;

create or replace function pressure_mbar(int /* raw pressure */, int /* raw temperature */, bytea /* calibration */) returns real
	as 'declare
			c1 int; c2 int; c3 int; c4 int; c5 int; c6 int;
			calib0 int; calib1 int; calib2 int; calib3 int;
			ut1 int;
			dt int;
			temp real;
			off real;
			sens real;
			x real;
			p real;
		begin
			-- parse calibration coefficiences out of byte array
			calib0 := get_byte($3, 0) & 255;
			calib0 := calib0 | (((get_byte($3, 1) & 255) << 8) & 65280);
			calib1 := get_byte($3, 2) & 255;
			calib1 := calib1 | (((get_byte($3, 3) & 255) << 8) & 65280);
			calib2 := get_byte($3, 4) & 255;
			calib2 := calib2 | (((get_byte($3, 5) & 255) << 8) & 65280);
			calib3 := get_byte($3, 6) & 255;
			calib3 := calib3 | (((get_byte($3, 7) & 255) << 8) & 65280);

			c1 := calib0 >> 1;
			c2 := calib2 & 63;
			c2 := c2 << 6;
			c2 := c2 | (calib3 & 63);
			c3 := calib3 >> 6;
			c4 := calib2 >> 6;
			c5 := calib0 << 10;
			c5 := c5 & 1024;
			c5 := c5 + (calib1 >> 6);
			c6 := calib1 & 63;

			ut1 := 8 * c5 + 20224;
			dt := $2 - ut1;
			temp := 200 + dt * (c6 + 50) / 1024;
			off := c2 * 4 + ((c4 - 512) * dt) / 4096;
			sens := c1 + (c3 * dt) / 1024 + 24576;
			x := (sens * ($1 - 7168)) / 16384 - off; 
			p := x * 100 / 32 + 250 * 100;
			return p / 100;
		end;'
	language 'plpgsql';

create or replace function pressure_inHg(int /* raw pressure */, int /* raw temperature */, bytea /* calibration */) returns real
	as 'declare
			c1 int; c2 int; c3 int; c4 int; c5 int; c6 int;
			calib0 int; calib1 int; calib2 int; calib3 int;
			ut1 int;
			dt int;
			off real;
			sens real;
			x real;
			p real;
		begin
			-- parse calibration coefficiences out of byte array
			calib0 := get_byte($3, 0) & 255;
			calib0 := calib0 | (((get_byte($3, 1) & 255) << 8) & 65280);
			calib1 := get_byte($3, 2) & 255;
			calib1 := calib1 | (((get_byte($3, 3) & 255) << 8) & 65280);
			calib2 := get_byte($3, 4) & 255;
			calib2 := calib2 | (((get_byte($3, 5) & 255) << 8) & 65280);
			calib3 := get_byte($3, 6) & 255;
			calib3 := calib3 | (((get_byte($3, 7) & 255) << 8) & 65280);

			c1 := calib0 >> 1;
			c2 := calib2 & 63;
			c2 := c2 << 6;
			c2 := c2 | (calib3 & 63);
			c3 := calib3 >> 6;
			c4 := calib2 >> 6;
			c5 := calib0 << 10;
			c5 := c5 & 1024;
			c5 := c5 + (calib1 >> 6);
			c6 := calib1 & 63;

			ut1 := 8 * c5 + 20224;
			dt := $2 - ut1;
			off := c2 * 4 + ((c4 - 512) * dt) / 4096;
			sens := c1 + (c3 * dt) / 1024 + 24576;
			x := (sens * ($1 - 7168)) / 16384 - off; 
			p := x * 100 / 32 + 250 * 100;
			return p / (100 * 33.864);
		end;'
	language 'plpgsql';

create or replace function pressure_temp(int /* raw temperature */, bytea /* calibration */) returns real
	as 'declare
			c1 int; c2 int; c3 int; c4 int; c5 int; c6 int;
			calib0 int; calib1 int; calib2 int; calib3 int;
			ut1 int;
			dt int;
			temp real;
		begin
			-- parse calibration coefficiences out of byte array
			calib0 := get_byte($2, 0) & 255;
			calib0 := calib0 | (((get_byte($2, 1) & 255) << 8) & 65280);
			calib1 := get_byte($2, 2) & 255;
			calib1 := calib1 | (((get_byte($2, 3) & 255) << 8) & 65280);
			calib2 := get_byte($2, 4) & 255;
			calib2 := calib2 | (((get_byte($2, 5) & 255) << 8) & 65280);
			calib3 := get_byte($2, 6) & 255;
			calib3 := calib3 | (((get_byte($2, 7) & 255) << 8) & 65280);

			c1 := calib0 >> 1;
			c2 := calib2 & 63;
			c2 := c2 << 6;
			c2 := c2 | (calib3 & 63);
			c3 := calib3 >> 6;
			c4 := calib2 >> 6;
			c5 := calib0 << 10;
			c5 := c5 & 1024;
			c5 := c5 + (calib1 >> 6);
			c6 := calib1 & 63;

			ut1 := 8 * c5 + 20224;
			dt := $1 - ut1;
			temp := 200 + dt * (c6 + 50) / 1024;
			return temp / 10;
		end;'
	language 'plpgsql';