Update PL/pgSQL trigger example to be clearer about how to "merge" data

bmomjian · bmomjian · commit 354213c7f493 · 2006-02-05T02:47:53.000Z
into a table.

Jim C. Nasby
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.83 2005/12/29 04:02:32 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.84 2006/02/05 02:47:53 momjian Exp $
 -->
 
 <chapter id="plpgsql"> 
@@ -3007,16 +3007,17 @@ CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $main
         END IF;
 
 
-        -- Update the summary row with the new values.
-        UPDATE sales_summary_bytime
-            SET amount_sold = amount_sold + delta_amount_sold,
-                units_sold = units_sold + delta_units_sold,
-                amount_cost = amount_cost + delta_amount_cost
-            WHERE time_key = delta_time_key;
+        -- Insert or update the summary row with the new values.
+        &lt;&lt;insert_update&gt;&gt;
+        LOOP
+            UPDATE sales_summary_bytime
+                SET amount_sold = amount_sold + delta_amount_sold,
+                    units_sold = units_sold + delta_units_sold,
+                    amount_cost = amount_cost + delta_amount_cost
+                WHERE time_key = delta_time_key;
 
+            EXIT insert_update WHEN found;    
 
-        -- There might have been no row with this time_key (e.g new data!).
-        IF (NOT FOUND) THEN
             BEGIN
                 INSERT INTO sales_summary_bytime (
                             time_key, 
@@ -3029,20 +3030,15 @@ CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $main
                             delta_units_sold,
                             delta_amount_cost
                            );
+
+                EXIT insert_update;
+
             EXCEPTION
-                --
-                -- Catch race condition when two transactions are adding data
-                -- for a new time_key.
-                --
                 WHEN UNIQUE_VIOLATION THEN
-                    UPDATE sales_summary_bytime
-                        SET amount_sold = amount_sold + delta_amount_sold,
-                            units_sold = units_sold + delta_units_sold,
-                            amount_cost = amount_cost + delta_amount_cost
-                        WHERE time_key = delta_time_key;
-
+                    -- do nothing
             END;
-        END IF;
+        END LOOP insert_update;
+
         RETURN NULL;
 
     END;
@@ -3051,6 +3047,16 @@ $maint_sales_summary_bytime$ LANGUAGE plpgsql;
 CREATE TRIGGER maint_sales_summary_bytime
 AFTER INSERT OR UPDATE OR DELETE ON sales_fact
     FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
+
+INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
+INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
+INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
+INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
+SELECT * FROM sales_summary_bytime;
+DELETE FROM sales_fact WHERE product_key = 1;
+SELECT * FROM sales_summary_bytime;
+UPDATE sales_fact SET units_sold = units_sold * 2;
+SELECT * FROM sales_summary_bytime;
 </programlisting>
    </example>
 
