Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit354213c

Browse files
committed
Update PL/pgSQL trigger example to be clearer about how to "merge" data
into a table.Jim C. Nasby
1 parent3893127 commit354213c

File tree

1 file changed

+26
-20
lines changed

1 file changed

+26
-20
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 26 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.83 2005/12/29 04:02:32 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.84 2006/02/0502:47:53 momjian Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -3007,16 +3007,17 @@ CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $main
30073007
END IF;
30083008

30093009

3010-
-- Update the summary row with the new values.
3011-
UPDATE sales_summary_bytime
3012-
SET amount_sold = amount_sold + delta_amount_sold,
3013-
units_sold = units_sold + delta_units_sold,
3014-
amount_cost = amount_cost + delta_amount_cost
3015-
WHERE time_key = delta_time_key;
3010+
-- Insert or update the summary row with the new values.
3011+
&lt;&lt;insert_update&gt;&gt;
3012+
LOOP
3013+
UPDATE sales_summary_bytime
3014+
SET amount_sold = amount_sold + delta_amount_sold,
3015+
units_sold = units_sold + delta_units_sold,
3016+
amount_cost = amount_cost + delta_amount_cost
3017+
WHERE time_key = delta_time_key;
30163018

3019+
EXIT insert_update WHEN found;
30173020

3018-
-- There might have been no row with this time_key (e.g new data!).
3019-
IF (NOT FOUND) THEN
30203021
BEGIN
30213022
INSERT INTO sales_summary_bytime (
30223023
time_key,
@@ -3029,20 +3030,15 @@ CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $main
30293030
delta_units_sold,
30303031
delta_amount_cost
30313032
);
3033+
3034+
EXIT insert_update;
3035+
30323036
EXCEPTION
3033-
--
3034-
-- Catch race condition when two transactions are adding data
3035-
-- for a new time_key.
3036-
--
30373037
WHEN UNIQUE_VIOLATION THEN
3038-
UPDATE sales_summary_bytime
3039-
SET amount_sold = amount_sold + delta_amount_sold,
3040-
units_sold = units_sold + delta_units_sold,
3041-
amount_cost = amount_cost + delta_amount_cost
3042-
WHERE time_key = delta_time_key;
3043-
3038+
-- do nothing
30443039
END;
3045-
END IF;
3040+
END LOOP insert_update;
3041+
30463042
RETURN NULL;
30473043

30483044
END;
@@ -3051,6 +3047,16 @@ $maint_sales_summary_bytime$ LANGUAGE plpgsql;
30513047
CREATE TRIGGER maint_sales_summary_bytime
30523048
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
30533049
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
3050+
3051+
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
3052+
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
3053+
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
3054+
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
3055+
SELECT * FROM sales_summary_bytime;
3056+
DELETE FROM sales_fact WHERE product_key = 1;
3057+
SELECT * FROM sales_summary_bytime;
3058+
UPDATE sales_fact SET units_sold = units_sold * 2;
3059+
SELECT * FROM sales_summary_bytime;
30543060
</programlisting>
30553061
</example>
30563062

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp