Unless I'm grossly misunderstanding the problem i think that a trigger written
in PL/pgsql would work fine. Something like this:
CREATE TABLE foo (
foo_id SERIAL primary key,
foo TEXT);
CREATE TABLE bar (
foo_id INTEGER references foo,
bar_id SERIAL primary key,
bar DOUBLE PRECISION NOT NULL);
CREATE TABLE bar_avg (
foo_id INTEGER primary key references foo,
bar_avg DOUBLE PRECISION);
CREATE OR REPLACE FUNCTION get_bar_avg() RETURNS TRIGGER AS '
DECLARE
bar_record RECORD;
x INTEGER;
y DOUBLE PRECISION := 0;
BEGIN
IF TG_OP = ''INSERT'' THEN
y := y + NEW.bar;
x := 1;
FOR bar_record IN SELECT * FROM bar LOOP
IF NEW.foo_id = bar_record.foo_id THEN
y := y + bar_record.bar;
x := x + 1;
END IF;
END LOOP;
y := y/x;
IF EXISTS(SELECT * FROM bar_avg WHERE foo_id = NEW.foo_id) THEN
UPDATE bar_avg
SET bar_avg.bar_avg = y
WHERE foo_id = NEW.foo_id;
ELSE
INSERT INTO bar_avg VALUES (NEW.foo_id, y);
END IF;
RETURN NEW;
ELSIF TG_OP = ''DELETE'' THEN
x := 0;
FOR bar_record IN SELECT * FROM bar LOOP
IF OLD.foo_id = bar_record.foo_id AND OLD.bar_id <>
bar_record.bar_id THEN
y := y + bar_record.bar;
x := x + 1;
END IF;
END LOOP;
IF x <> 0 THEN
y := y/x;
END IF;
UPDATE bar_avg
SET bar_avg.bar_avg = y
WHERE foo_id = OLD.foo_id;
RETURN OLD;
ELSE
y := y + NEW.bar;
x := 1;
FOR bar_record IN SELECT * FROM bar LOOP
IF OLD.bar_id <> bar_record.bar_id THEN
y := y + bar_record.bar;
x := x + 1;
END IF;
END LOOP;
y := y/x;
UPDATE bar_avg
SET bar_avg.bar_avg = y
WHERE foo_id = OLD.foo_id;
RETURN NEW;
END IF;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER get_bar_avg BEFORE INSERT OR DELETE OR UPDATE ON bar FOR EACH ROW
EXECUTE PROCEDURE get_bar_avg();
That should work,
regards,
matt
Quoting "Jim C. Nasby" <decibel@xxxxxxxxxxx>:
> On Fri, Jan 21, 2005 at 12:53:45AM -0500, Greg Stark wrote:
> > "Vanole, Mike" <Mike.Vanole@xxxxxxxxxxxx> writes:
> >
> > > I need to calculate a moving average and I would like to do it with
> SQL,
> > > or a Pg function built for this purpose. I'm on Pg 7.4. Is this
> possible
> > > in Pg without a bunch of self joins, or is there a funtion available?
> >
> > Unfortunately moving averages fall into a class of functions, called
> analytic
> > functions (at least that's what Oracle calls them) that are inherently hard
> to
> > model efficiently in SQL. Postgres doesn't have any special support for
> this
> > set of functions, so you're stuck doing it the inefficient ways that
> standard
> > SQL allows.
> >
> > I think this is even hard to implement correctly using Postgres's
> extremely
> > extensible function support. Even if you implemented it in Perl or Python
> I
> > don't think there's any way to allocate a temporary static storage area for
> a
> > given call site. So your moving average function would behave strangely if
> you
> > called it twice in a given query.
> >
> > But if you can work within that caveat it should be straightforward to
> > implement it efficiently in Perl or Python. Alternatively you can write a
> > plpgsql function to calculate the specific moving average you need that
> does
> > the select itself.
>
> If you're feeling adventurous, you might look at Oracle's documentation
> on their analytic functions and see if you can come up with something
> generic for PostgreSQL. Even if you only do a moving average function it
> would be a good start.
> --
> Jim C. Nasby, Database Consultant decibel@xxxxxxxxxxx
> Give your computer some brain candy! www.distributed.net Team #1828
>
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
