2NxgQApL

1-- Ignores duplicates.
2  INSERT INTO
3    db_table (tbl_column_1, tbl_column_2)
4  VALUES (
5    SELECT
6      unnseted_column,
7      param_association
8    FROM
9      unnest( param_array_ids ) AS unnested_column
10  );
11	
12BEGIN
13    INSERT INTO db_table (tbl_column) VALUES (v_tbl_column);
14  EXCEPTION WHEN unique_violation THEN
15    -- Ignore duplicate inserts.
16  END;
17	
18CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
19    ON INSERT TO db_table
20   WHERE (EXISTS ( SELECT 1
21           FROM db_table
22          WHERE db_table.tbl_column = NEW.tbl_column)) DO INSTEAD NOTHING;
23	
24o /dev/null
25timing off
26
27-- set up data
28DROP TABLE IF EXISTS insert_test;
29
30CREATE TABLE insert_test_base_data (
31    id integer PRIMARY KEY,
32    col1 double precision,
33    col2 text
34);
35
36CREATE TABLE insert_test (
37    id integer PRIMARY KEY,
38    col1 double precision,
39    col2 text
40);
41
42INSERT INTO insert_test_base_data
43SELECT i, (SELECT random() AS r WHERE s.i = s.i)
44FROM 
45    generate_series(2, 200, 2) s(i)
46;
47
48UPDATE insert_test_base_data
49SET col2 = md5(col1::text)
50;
51
52INSERT INTO insert_test
53SELECT *
54FROM insert_test_base_data
55;
56
57
58
59-- function with exception block to be called later
60CREATE OR REPLACE FUNCTION f_insert_test_insert(
61    id integer,
62    col1 double precision,
63    col2 text
64)
65RETURNS void AS
66$body$
67BEGIN
68    INSERT INTO insert_test
69    VALUES ($1, $2, $3)
70    ;
71EXCEPTION
72    WHEN unique_violation
73    THEN NULL;
74END;
75$body$
76LANGUAGE plpgsql;
77
78
79
80-- function running plain SQL ... WHERE NOT EXISTS ...
81CREATE OR REPLACE FUNCTION insert_test_where_not_exists()
82RETURNS void AS
83$body$
84BEGIN
85    FOR i IN 1 .. 100
86    LOOP
87        INSERT INTO insert_test
88        SELECT i, rnd, md5(rnd::text)
89        FROM (SELECT random() AS rnd) r
90        WHERE NOT EXISTS (
91            SELECT 1
92            FROM insert_test
93            WHERE id = i
94        )
95        ;
96    END LOOP;
97END;
98$body$
99LANGUAGE plpgsql;
100
101
102
103-- call a function with exception block
104CREATE OR REPLACE FUNCTION insert_test_function_with_exception_block()
105RETURNS void AS
106$body$
107BEGIN
108    FOR i IN 1 .. 100
109    LOOP
110        PERFORM f_insert_test_insert(i, rnd, md5(rnd::text))
111        FROM (SELECT random() AS rnd) r
112        ;
113    END LOOP;
114END;
115$body$
116LANGUAGE plpgsql;
117
118
119
120-- leave checking existence to a rule
121CREATE OR REPLACE FUNCTION insert_test_rule()
122RETURNS void AS
123$body$
124BEGIN
125    FOR i IN 1 .. 100
126    LOOP
127        INSERT INTO insert_test
128        SELECT i, rnd, md5(rnd::text)
129        FROM (SELECT random() AS rnd) r
130        ;
131    END LOOP;
132END;
133$body$
134LANGUAGE plpgsql;
135
136
137
138o
139timing on
140
141
142echo 
143echo 'check before INSERT'
144
145SELECT insert_test_where_not_exists();
146
147echo 
148
149
150
151o /dev/null
152
153timing off
154
155TRUNCATE insert_test;
156
157INSERT INTO insert_test
158SELECT *
159FROM insert_test_base_data
160;
161
162timing on
163
164o
165
166echo 'catch unique-violation'
167
168SELECT insert_test_function_with_exception_block();
169
170echo 
171echo 'implementing a RULE'
172
173o /dev/null
174timing off
175
176TRUNCATE insert_test;
177
178INSERT INTO insert_test
179SELECT *
180FROM insert_test_base_data
181;
182
183CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
184    ON INSERT TO insert_test
185    WHERE EXISTS ( 
186        SELECT 1
187        FROM insert_test
188        WHERE id = NEW.id
189    ) 
190    DO INSTEAD NOTHING;
191
192o 
193timing on
194
195SELECT insert_test_rule();