Re: Another Plpgsql trigger example - summary table - Mailing list pgsql-patches

Mark Kirkwood wrote:
>
> Looking at option 2, it seems reasonable to add a trimmed trigger
> example into the plpgsql examples section now, and leave the data
> warehouse introductory stuff for its own chapter at some later stage.
>
Sorry about this -

A amendment so as not not require a SELECT from the main table in the
trigger procedure. This makes the intent of the code more obvious, and
makes more sense within the context of the example.

Should perform better too.

Thanks to Simon for a mail that made me think about this a bit more.

regards

Mark

--- plpgsql.sgml.orig    Sat Jan 15 14:55:39 2005
+++ plpgsql.sgml    Sat Jan 15 14:57:50 2005
@@ -2787,17 +2787,12 @@
                             amount_sold,
                             units_sold,
                             amount_cost)
-                    SELECT  f.time_key,
-                            sum(f.amount_sold),
-                            sum(f.units_sold),
-                            sum(f.amount_cost)
-                    FROM sales_fact f
-                    WHERE f.time_key = delta_time_key
-                    GROUP BY f.time_key;
-                -- This query can potentially be very expensive if the trigger
-                -- is created on sales_fact without the time_key indexes.
-                -- Some care is needed to ensure that this situation does
-                -- *not* occur.
+                    VALUES (
+                            delta_time_key,
+                            delta_amount_sold,
+                            delta_units_sold,
+                            delta_amount_cost
+                           );
             EXCEPTION
                 --
                 -- Catch race condition when two transactions are adding data