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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
