diff options
| author | Bruce Momjian <bruce@momjian.us> | 2006-02-05 02:48:20 +0000 |
|---|---|---|
| committer | Bruce Momjian <bruce@momjian.us> | 2006-02-05 02:48:20 +0000 |
| commit | 3bf6284f10bef844119aad19f66b7f77b625c2eb (patch) | |
| tree | 96866f7cd9257de6a74c1b08aebfed54868d5930 /doc/src | |
| parent | 180b23cb220c00180b1a2459ad0676ad336ad7fb (diff) | |
| download | postgresql-3bf6284f10bef844119aad19f66b7f77b625c2eb.tar.gz postgresql-3bf6284f10bef844119aad19f66b7f77b625c2eb.zip | |
Update PL/pgSQL trigger example to be clearer about how to "merge" data
into a table.
Jim C. Nasby
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/plpgsql.sgml | 46 |
1 files changed, 26 insertions, 20 deletions
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 729abf54aa1..9300eda0086 100644 --- 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.80.2.3 2005/12/29 04:02:41 momjian Exp $ +$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.80.2.4 2006/02/05 02:48:20 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. + <<insert_update>> + 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> |