-
Notifications
You must be signed in to change notification settings - Fork 0
/
trigger.sql
55 lines (47 loc) · 1.28 KB
/
trigger.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
begin;
create or replace function geo.tg_geometry_notify ()
returns trigger
language plpgsql
as $$
declare
channel text := TG_ARGV[0];
begin
PERFORM (
with payload(id, tablename, op) as
(
select
(case when NEW is NULL THEN OLD.id ELSE NEW.id END) as id,
TG_TABLE_NAME,
TG_OP
)
select pg_notify(channel, payload::text)
from payload
);
RETURN NULL;
end;
$$;
DROP TRIGGER IF EXISTS geometry_modify
ON geo.gz_polygon;
DROP TRIGGER IF EXISTS geometry_modify
ON geo.gz_line;
DROP TRIGGER IF EXISTS geometry_modify
ON geo.gz_circle;
CREATE TRIGGER geometry_modify
AFTER INSERT OR UPDATE OR DELETE
ON geo.gz_polygon
FOR EACH ROW
EXECUTE PROCEDURE geo.tg_geometry_notify('geometry_modify');
CREATE TRIGGER geometry_modify
AFTER INSERT OR UPDATE OR DELETE
ON geo.gz_line
FOR EACH ROW
EXECUTE PROCEDURE geo.tg_geometry_notify('geometry_modify');
CREATE TRIGGER geometry_modify
AFTER INSERT OR UPDATE OR DELETE
ON geo.gz_circle
FOR EACH ROW
EXECUTE PROCEDURE geo.tg_geometry_notify('geometry_modify');
commit;
--listen "geometry_modify";
--insert into geo.gz_polygon(gz_id, polygon) values (1, ST_AsBinary(ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')));
--delete from geo.gz_polygon where id=7625;