View source for Toolbox - Page Delete Problem

Copies of Toolbox databases may not have plpgsql functions for doing certain things.

Use the code below to create the "delete_subtree" plpgsql function.

<pre>

DROP TABLE IF EXISTS WorkingTable;
CREATE LOCAL TEMPORARY TABLE WorkingTable
(id INTEGER NOT NULL)
ON COMMIT DELETE ROWS;

CREATE OR REPLACE FUNCTION toolbox.delete_subtree (IN dead_guy INTEGER) RETURNS void AS $$
        DECLARE
                old_pos INTEGER; -- current position of the target node
                old_par INTEGER; -- current parent of the target node
        BEGIN
                -- defer consraint that forbids (parent = id)
                SET CONSTRAINTS ALL DEFERRED;

                --       set the old_pos and old_par variable
        SELECT pos
          INTO old_pos
          FROM toolbox.pages
         WHERE id = dead_guy;

        SELECT parent
          INTO old_par
          FROM toolbox.pages
         WHERE id = dead_guy;

                -- mark root of subtree and immediate subordinates
                UPDATE toolbox.pages
                   SET id  = CASE WHEN id = dead_guy
                                         THEN -99999 ELSE id END,
                           parent = CASE WHEN parent = dead_guy
                                                THEN -99999 ELSE parent END
                 WHERE dead_guy IN (id, parent);

                WHILE EXISTS -- mark leaf nodes
                         (SELECT *
                           FROM toolbox.pages
                          WHERE parent = -99999
                                AND id > -99999)
                LOOP -- get list of next level subordinates

                                DELETE FROM WorkingTable;
                                INSERT INTO WorkingTable
                                SELECT id FROM toolbox.pages WHERE parent = -99999;

                        -- delete old markers so we don't keep an infinite loop
                         DELETE FROM toolbox.pages
                          WHERE parent = -99999;

                        -- mark next level of subordinates
                         UPDATE toolbox.pages
                                SET parent = -99999
                          WHERE parent IN (SELECT id FROM WorkingTable);

                END LOOP;

                -- delete all marked nodes
                DELETE FROM toolbox.pages
                 WHERE id = -99999;

                -- reset all the positions at the target nodes level
        UPDATE toolbox.pages
           SET pos    = pos - 1
         WHERE parent = old_par
           AND pos    > old_pos;

                SET CONSTRAINTS ALL IMMEDIATE;
        END;
$$ LANGUAGE plpgsql;
</pre>