-
Notifications
You must be signed in to change notification settings - Fork 1
/
install.sql
53 lines (44 loc) · 1.54 KB
/
install.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
CREATE OR REPLACE FUNCTION {{= it.names.notifyTriggerFunc }}() RETURNS trigger AS $$
DECLARE
data text := '{}';
BEGIN
{{? it.checkUpdates }}
IF TG_OP = 'UPDATE' AND OLD IS NOT DISTINCT FROM NEW THEN
RETURN NULL;
END IF;
{{?}}
data := '{"table": "' || TG_TABLE_NAME || '",
"operation": "' || TG_OP || '",
"timestamp": "' || CURRENT_TIMESTAMP || '"';
{{ var id = it.sendRecordId; }}
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
data := data || ', "data": ' ||
{{?id}} '{ "{{=id}}": ' || NEW.{{=id}}::text || ' }'; {{??}} row_to_json(NEW); {{?}}
END IF;
IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
data := data || ', "old_data": ' ||
{{?id}} '{ "{{=id}}": ' || OLD.{{=id}}::text || ' }'; {{??}} row_to_json(OLD); {{?}}
END IF;
data := data || '}';
PERFORM pg_notify('{{= it.changesChannel }}', data);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION {{= it.names.addNotifyTrigger }}(table_name text)
RETURNS VOID as $$
BEGIN
EXECUTE 'DROP TRIGGER IF EXISTS {{= it.names.notifyTrigger }} ON ' || table_name::regclass;
EXECUTE '
CREATE TRIGGER {{= it.names.notifyTrigger }}
AFTER INSERT OR UPDATE OR DELETE
ON ' || table_name::regclass || '
FOR EACH ROW
EXECUTE PROCEDURE {{= it.names.notifyTriggerFunc }}()';
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION {{= it.names.removeNotifyTrigger }}(table_name text)
RETURNS VOID AS $$
BEGIN
EXECUTE 'DROP TRIGGER IF EXISTS {{= it.names.notifyTrigger }} ON ' || table_name::regclass;
END;
$$ LANGUAGE plpgsql;