foo.sql

create or replace function t_container_id() returns trigger as $weight$
    declare
        container_id_changed CONSTANT boolean := TG_OP = 'UPDATE' AND NEW.container_id IS DISTINCT FROM OLD.container_id;
        compute_new_weight CONSTANT boolean := TG_OP = 'INSERT' OR container_id_changed;
    begin
        if container_id_changed then
            -- Prevent concurrent modifications.
            PERFORM 1
            FROM folder
            WHERE content_id = NEW.container_id
            FOR UPDATE;

            PERFORM 1
            FROM folder
            WHERE content_id = NEW.id
            FOR UPDATE;

            IF FOUND THEN

                /*
                    Imagine we have the following:

                           A
                          / \
                        B     C
                       / \   /
                      D   F G
                     /     \
                    E       H
                           / \
                          I   J
               
                    We must ensure that the NEW.container_id is not part of it's 
                    lower hierarchy. 

                    For example an error must be raised if:
                    - we update B and NEW.container_id equals to the id 
                      of any D,F,E,H,I,J
                    - we update F and NEW.container_id equals to the id 
                      of any H,I,J
                    - ...

                    The query below checks that if we update B.container_id the 
                    NEW.container_id is not a child of B.
                */
                
                IF EXISTS (
                    WITH RECURSIVE children AS (
                            SELECT c1.id
                            FROM content c1
                            JOIN folder f1 ON c1.id = f1.content_id 
                            WHERE c1.container_id = NEW.id
                        UNION ALL 
                            SELECT c2.id
                            FROM content c2 
                            JOIN folder f2 ON c2.id = f2.content_id 
                            JOIN children ch ON ch.id = c2.container_id
                    ) 

                    SELECT * FROM children WHERE id = NEW.container_id
                ) 
                THEN
                    RAISE EXCEPTION '% is a child of %', NEW.container_id, 
                        NEW.id;
                END IF;
                
            END IF;  -- FOUND

        END IF;  -- container_id_changed

        IF compute_new_weight THEN
            NEW.weight := (
                SELECT coalesce(max(weight) + 1, 1)
                FROM content
                WHERE container_id = NEW.container_id
            );

            RAISE NOTICE 'weight of % within container % set to %', NEW.id, 
                NEW.container_id, NEW.weight;
        END IF;  -- compute_new_weight

        return NEW;
    end;
$weight$ language plpgsql;

create trigger t_container_id before insert or update on content
    for each row execute procedure t_container_id();