MP3zdPLx

1CREATE OR REPLACE FUNCTION public.t_file_obj_pic_id()
2RETURNS trigger AS
3$$
4-- Trigger function to update obj table (obj_main_pic_obj_id column) with the obj_id of just inserted file
5DECLARE
6    new_obj_main_pic_obj_id int8;
7    old_obj_main_pic_obj_id int8;
8    obj_type TEXT;
9BEGIN
10    -- vdm: Is it just an update of comments count?
11    IF TG_OP = 'UPDATE' THEN IF NEW.obj_comments_count <> OLD.obj_comments_count THEN RETURN NEW; END IF; END IF;
12
13    SELECT get_object_type(NEW.file_obj_obj_id) INTO obj_type;
14
15    IF obj_type = 'place' THEN
16        -- vdm: Nothing to do in this case as we will set +1 for this photo in application
17        IF TG_OP = 'INSERT' THEN RETURN NEW; END IF;
18        
19        -- vdm: nothing changed to start update of place.obj_main_pic_obj_id
20        IF NEW.obj_marks_count = OLD.obj_marks_count AND NEW.obj_status_did = OLD.obj_status_did AND NEW.file_obj_obj_id = OLD.file_obj_obj_id THEN RETURN NEW; END IF;
21        
22        -- vdm: set most rated photo as place's main photo
23        SELECT obj_id INTO new_obj_main_pic_obj_id FROM file WHERE file_obj_obj_id = NEW.file_obj_obj_id AND obj_status_did = 1 ORDER BY obj_marks_sum_sign DESC, obj_created DESC LIMIT 1;
24        SELECT obj_main_pic_obj_id INTO old_obj_main_pic_obj_id FROM obj WHERE obj_id = NEW.file_obj_obj_id;
25    
26        IF new_obj_main_pic_obj_id IS NULL OR old_obj_main_pic_obj_id IS NULL OR new_obj_main_pic_obj_id != old_obj_main_pic_obj_id THEN
27            UPDATE place SET obj_main_pic_obj_id = new_obj_main_pic_obj_id where obj_id = NEW.file_obj_obj_id;
28        END IF;
29        
30        -- vdm: this is places merge case. Just delete main_pic_obj_id for old place
31        IF NEW.file_obj_obj_id <> OLD.file_obj_obj_id THEN
32            UPDATE place SET obj_main_pic_obj_id = null where obj_id = OLD.file_obj_obj_id; 
33        END IF;
34    ELSE
35        IF TG_OP = 'UPDATE' THEN
36            -- vdm: file_obj_obj_id changed
37            IF NEW.file_obj_obj_id <> OLD.file_obj_obj_id THEN
38                -- vdm: it is allowed only for group type house blog
39                IF obj_type <> 'group' OR (SELECT count(1) FROM "group" WHERE obj_id IN (NEW.file_obj_obj_id, OLD.file_obj_obj_id) AND group_type_did = 69000001667 /* group_type_house_blog */) <> 2 THEN 
40                   RAISE EXCEPTION 'file_obj_obj_id is readonly';
41                END IF;
42                -- vdm: calc new main pic for new photo's group 
43                UPDATE "group" SET obj_main_pic_obj_id = (SELECT obj_id FROM file WHERE file_obj_obj_id = NEW.file_obj_obj_id AND obj_status_did = 1 ORDER BY obj_created DESC LIMIT 1) WHERE obj_id = NEW.file_obj_obj_id;
44                -- vdm: calc new main pic for old photo's group
45                UPDATE "group" SET obj_main_pic_obj_id = (SELECT obj_id FROM file WHERE file_obj_obj_id = OLD.file_obj_obj_id AND obj_status_did = 1 ORDER BY obj_created DESC LIMIT 1) WHERE obj_id = OLD.file_obj_obj_id AND obj_main_pic_obj_id = OLD.obj_id;
46                RETURN NEW;
47            END IF; 
48        END IF;
49    
50        -- vdm: update, but status not changed. Nothing to do.
51        IF TG_OP = 'UPDATE' THEN IF NEW.obj_status_did = OLD.obj_status_did THEN RETURN NEW; END IF; END IF;
52
53        -- vdm: new disabled photo
54        IF TG_OP = 'INSERT' AND NEW.obj_status_did <> 1 THEN RETURN NEW; END IF;
55
56        -- It is photo from group type house blog
57        IF obj_type = 'group' AND EXISTS (SELECT 1 FROM "group" WHERE obj_id = NEW.file_obj_obj_id AND group_type_did = 69000001667 /* group_type_house_blog */) THEN
58            IF TG_OP = 'INSERT' THEN
59                UPDATE "group" SET obj_main_pic_obj_id = NEW.obj_id WHERE obj_id = NEW.file_obj_obj_id;
60            ELSE
61                UPDATE 
62                    "group"
63                SET
64                    obj_main_pic_obj_id = (SELECT obj_id FROM file WHERE file_obj_obj_id = NEW.file_obj_obj_id AND obj_status_did = 1 ORDER BY obj_created DESC LIMIT 1)
65                WHERE
66                    obj_id = NEW.file_obj_obj_id;
67            END IF;
68        -- Somebody trying to delete object's main photo
69        ELSEIF TG_OP = 'UPDATE' THEN
70            IF OLD.obj_status_did = 1 THEN
71                -- vdm: workaround for stupid postgresql at .prod
72                IF obj_type = 'person' THEN
73                    --UPDATE person SET obj_main_pic_obj_id = NULL, person_photo_is_best = false WHERE obj_id = NEW.file_obj_obj_id AND obj_main_pic_obj_id = NEW.obj_id;
74                ELSEIF obj_type = 'group' THEN
75                    UPDATE "group" SET obj_main_pic_obj_id = NULL WHERE obj_id = NEW.file_obj_obj_id AND obj_main_pic_obj_id = NEW.obj_id;
76                ELSE
77                    UPDATE obj SET obj_main_pic_obj_id = NULL WHERE obj_id = NEW.file_obj_obj_id AND obj_main_pic_obj_id = NEW.obj_id; 
78                END IF;
79            END IF;
80        END IF;
81    END IF;
82    RETURN NEW;
83END;
84$$
85LANGUAGE 'plpgsql'
86VOLATILE
87CALLED ON NULL INPUT
88SECURITY INVOKER
89COST 100;