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
| CREATE OR REPLACE FUNCTION master_foreign_key() RETURNS TRIGGER AS $$
DECLARE
curs refcursor;
id int;
idchild integer;
BEGIN
IF TG_NARGS <> 3 THEN
RAISE EXCEPTION 'master_foreign_key need to receive 3 parameters';
END IF;
IF TG_WHEN = 'AFTER' THEN
idchild := (hstore(new)->TG_ARGV[0])::bigint;
OPEN curs FOR EXECUTE 'SELECT * FROM ' || quote_ident(TG_ARGV[1]) || ' WHERE ' || quote_ident(TG_ARGV[2]) || ' = $1' USING idchild;
FETCH curs INTO id;
IF NOT FOUND THEN
RAISE EXCEPTION '% on table "%" violates master_foreign_key
DETAIL: Key (idobject)=(%) is not present in table "%"', TG_OP, TG_RELNAME, idchild, TG_ARGV[1];
END IF;
CLOSE curs;
RETURN NEW;
ELSE
idchild := (hstore(old)->TG_ARGV[0])::bigint;
OPEN curs FOR EXECUTE 'SELECT * FROM ' || quote_ident(TG_ARGV[1]) || ' WHERE ' || quote_ident(TG_ARGV[2]) || ' = $1' USING idchild;
FETCH curs INTO id;
IF FOUND THEN
RAISE EXCEPTION '% on table "%" violates master_foreign_key
DETAIL: Key (idobject)=(%) is still referenced from table "%"', TG_OP, TG_RELNAME, idchild, TG_ARGV[1];
END IF;
CLOSE curs;
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql; |
