On Tuesday 22 December 2009 5:46:25 am Wappler, Robert wrote:
>
> Assuming we could pass a bulk of rows as a table, the update could be
> performed as follows:
>
> CREATE OR REPLACE FUNCTION update_tpl(measurements SETOF sensor_data)
> RETURNS void VOLATILE AS $$ UPDATE temperature_per_location AS tpl
> SET (avg_temperature, no_of_measurements) =
> ((tpl.no_of_measurements * tpl.temperature +
> m.temperature)/(tpl.no_of_measurements
> + COUNT(*) OVER(PARTITION BY l.location)), tpl.no_of_measurements +
> COUNT(*) OVER (PARTITION BY l.location)) FROM measurements m JOIN
> sensor_location l USING (sensor_id)
> WHERE tpl.location = l.location;
> $$ LANGUAGE SQL;
>
> The second version wouldn't need an enclosing loop, it could also use
> directly a combined index on sensor_location (sensor_id, location) to
> optimize the iteration performed in the update. The code for the invocation
> may be
>
> SELECT * FROM update_tpl(select_batch());
>
Not quite what you want, but would the below work?
CREATE OR REPLACE FUNCTION update_tpl() RETURNS void VOLATILE AS $$
UPDATE temperature_per_location AS tpl
SET (avg_temperature, no_of_measurements) =
((tpl.no_of_measurements * tpl.temperature +
m.temperature)/(tpl.no_of_measurements
+ COUNT(*) OVER(PARTITION BY
l.location)), tpl.no_of_measurements + COUNT(*) OVER (PARTITION BY
l.location))
FROM select_batch() m JOIN sensor_location l USING (sensor_id)
^^^^^^^^^^^^^^
WHERE tpl.location = l.location;
$$ LANGUAGE SQL;
--
Adrian Klaver
aklaver@comcast.net