On Fri, Jan 06, 2006 at 02:00:34PM +1300, Mark Kirkwood wrote:
> However, I think the actual change is not quite right - after running
DOH! It would be good if doc/src had a better mechanism for handling
code; one that would allow for writing the code natively (so you don't
have to worry about translating < into < and > into >) and for
unit testing the different pieces of code.
Anyway, updated patch attached.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.83
diff -c -r1.83 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml 29 Dec 2005 04:02:32 -0000 1.83
--- doc/src/sgml/plpgsql.sgml 6 Jan 2006 03:03:11 -0000
***************
*** 3007,3022 ****
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;
- -- 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,
--- 3007,3023 ----
END IF;
! -- 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;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
***************
*** 3029,3048 ****
delta_units_sold,
delta_amount_cost
);
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;
!
END;
! END IF;
RETURN NULL;
END;
--- 3030,3044 ----
delta_units_sold,
delta_amount_cost
);
+
+ EXIT insert_update;
+
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
! -- do nothing
END;
! END LOOP insert_update;
!
RETURN NULL;
END;
***************
*** 3051,3056 ****
--- 3047,3062 ----
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>
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
