|
5005
|
1 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
|
|
|
2 $$
|
|
|
3 DECLARE
|
|
|
4 new_geom geometry;
|
|
|
5 tg_arg text;
|
|
|
6 filters text;
|
|
|
7 has_equal boolean;
|
|
|
8 BEGIN
|
|
|
9 EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
|
|
|
10 INTO new_geom
|
|
|
11 USING NEW;
|
|
|
12 FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP
|
|
|
13 -- Test each additional argument for equality
|
|
|
14 filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg);
|
|
|
15 END LOOP;
|
|
|
16 EXECUTE format(
|
|
|
17 'SELECT EXISTS(SELECT 1 FROM %I.%I '
|
|
|
18 'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))'
|
|
|
19 '%s)',
|
|
|
20 TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters)
|
|
|
21 INTO has_equal
|
|
|
22 USING new_geom, NEW;
|
|
|
23 IF has_equal THEN
|
|
|
24 RAISE EXCEPTION
|
|
|
25 'new row for relation "%" violates constraint trigger "%"',
|
|
|
26 TG_TABLE_NAME, TG_NAME
|
|
|
27 USING
|
|
|
28 DETAIL = format('Failing row contains geometry in %s',
|
|
|
29 Box2D(new_geom)),
|
|
|
30 ERRCODE = 23505,
|
|
|
31 SCHEMA = TG_TABLE_SCHEMA,
|
|
|
32 TABLE = TG_TABLE_NAME,
|
|
|
33 COLUMN = TG_ARGV[0],
|
|
|
34 CONSTRAINT = TG_NAME;
|
|
|
35 END IF;
|
|
|
36 RETURN NEW;
|
|
|
37 END;
|
|
|
38 $$
|
|
|
39 LANGUAGE plpgsql;
|
