Recursive update for tree structure in PostgreSQL

I'd came to a solution that uses a trigger to manage the updates properly. To make things better and easier, I added the column chpath that is an array of integer, to keep the value presented by you as chnum. This seems more reasonable to me, as we can order by it correctly (which cannot be done with string, at least no without some treatment) and also is easier to manipulate.

So, I came to this table:

CREATE TABLE chapters (
    id serial primary key,
    chnum text,
    parentid integer,
    chpath integer[]
);

Now to the problem...

The following trigger function is capable of managing the insert and delete of rows:

CREATE OR REPLACE FUNCTION tg_chapters()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
    v_head int[];
    v_len int;
BEGIN
    IF (TG_WHEN = 'BEFORE' AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN
        /* Update chnum field (string representation of chpath) */
        NEW.chnum = array_to_string(NEW.chpath, '.');
    END IF;
    IF (TG_WHEN = 'AFTER' AND TG_OP = 'DELETE') THEN
        v_len := array_length(OLD.chpath, 1);
        /* Get the chapters with same head and DECREMENT the last level */
        UPDATE chapters c
        SET chpath = c.chpath[1:(v_len-1)]||(c.chpath[v_len]-1)||c.chpath[(v_len+1):array_upper(c.chpath,1)]
        WHERE
            c.id <> OLD.id
            AND c.chpath >= OLD.chpath
            AND c.chpath[1:v_len] <> OLD.chpath
            AND array_length(c.chpath,1) >= v_len;
        /* Delete the children */
        DELETE FROM chapters c WHERE c.parentid = OLD.id;
    END IF;
    IF (TG_OP = 'INSERT') THEN
        IF (TG_WHEN = 'BEFORE') THEN
            v_head := NEW.chpath[1:(array_upper(NEW.chpath,1)-1)];
            /* BEFORE: update chpath and parentid */
            IF (v_head = '{}'::int[]) THEN
                NEW.parentid = null;
            ELSE
                SELECT c.id INTO STRICT NEW.parentid FROM chapters c WHERE c.id <> NEW.id AND c.chpath = v_head;
            END IF;
        ELSIF (TG_WHEN = 'AFTER') THEN
            v_len := array_length(NEW.chpath, 1);
            /* AFTER: get the chapters with same head and INCREMENT the last level */
            UPDATE chapters c
            SET chpath = c.chpath[1:(v_len-1)]||(c.chpath[v_len]+1)||c.chpath[(v_len+1):array_upper(c.chpath,1)]
            WHERE
                c.id <> NEW.id
                AND c.chpath >= NEW.chpath
                AND array_length(c.chpath,1) >= v_len;
        END IF;
    END IF;
    RETURN NEW;
END;
$function$;

And the triggers:

CREATE TRIGGER tg_chapters_after
AFTER INSERT OR DELETE
ON chapters
FOR EACH ROW EXECUTE PROCEDURE tg_chapters();
CREATE TRIGGER tg_chapters_before
BEFORE INSERT OR UPDATE OF chpath
ON chapters
FOR EACH ROW EXECUTE PROCEDURE tg_chapters();

It only uses the chpath, so the insert must use it properly, and you should always insert into a valid position (e.g. it does not treat gaps). It could be improved to provide some constraints. Also, don't try bulk insert or bulk delete, because this trigger expect the rows to be inserted/deleted in the correct order.

Now, about the lack of update, if you really need an UPDATE you can:

1. Try to improve the trigger with some kind of flag column;
2. Use DELETE followed by INSERT instead of an UPDATE (inside a transaction, of course).

The first seems a little hard at first glance and error prone. The second is simple and works well, to avoid unusual operations on the application side you could create a view to this table (and use only it) that handle the INSERT/DELETE (just issue them on the real table) and UPDATE (do DELETE + INSERT on the real table).

Samples at this SQL Fiddle.