Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following structure of table.

id     chNum        parentid 
---    ------       --------- 
1        1            NULL
2        1.1          1
3        1.1.1        2
4        2            NULL  (Here add the new row) 
5        3            NULL
6        4            NULL

After added new row in between the rows, this and all the following rows (based on chNum) should change.

Result as follows:

id     chNum        parentid 
---    ------       --------- 
1        1            NULL
2        1.1          1
3        1.1.1        2
4        2            NULL  
7        3            NULL --\
5        4            NULL -- + -- these rows are changed
6        5            NULL --/

Is this possible to use recursive update SQL query in pl/pgsql and is this best way for faster data update than through application side?

Sample scenarios for "chNum" auto update:

enter image description here

share|improve this question
I don't see here the need for recursion. But there is an other problem: what if a value to be changed is in the form of 3.3 or 2.3.4? –  dezso Sep 11 '13 at 5:28
Yes. Possible. If I add as parent, shows the above example. As you said, its possible to add as child like "From 2 to 2.1, 2.2, 2.3...." –  gnabhan Sep 11 '13 at 5:36
What I mean is that if the row with the ID 5 contained 3.3 instead of 3, what should it change to? And what should happen when you insert a new child? –  dezso Sep 11 '13 at 7:45
Here ID is unique not changed. If insert a row as parent "ChNum" column automatically increased. The same time, if delete any row, that time also "chNum" automatically rearranged. –  gnabhan Sep 11 '13 at 8:10

2 Answers 2

up vote 0 down vote accepted

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:

RETURNS trigger
LANGUAGE plpgsql
AS $function$
    v_head int[];
    v_len int;
        /* Update chnum field (string representation of chpath) */
        NEW.chnum = array_to_string(NEW.chpath, '.');
    END IF;
        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)]
            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_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;
                SELECT c.id INTO STRICT NEW.parentid FROM chapters c WHERE c.id <> NEW.id AND c.chpath = v_head;
            END IF;
            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)]
                c.id <> NEW.id
                AND c.chpath >= NEW.chpath
                AND array_length(c.chpath,1) >= v_len;
        END IF;
    END IF;

And the triggers:

CREATE TRIGGER tg_chapters_after
ON chapters
CREATE TRIGGER tg_chapters_before
ON 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.

share|improve this answer
BTW, if someone (@dezso ?) have a better an idea about the update, I'd like to read... =D –  MatheusOl Sep 12 '13 at 5:24
Thanks lot @MatheusOl...few things confirm is it possible. One is: chNum order should be incremental order. like 1.1,1.2,1.3... Not jump to 1.1, 1.4 like that. Is this possible ? Second one is: is possible to move row between list. For e.g: {4} move to {1.3}. I want keep primary key number. bocz I have related data with this number. –  gnabhan Sep 13 '13 at 5:04
@gnabhan: First, with this implementation chNum doesn't jump, unless I made some mistake on the implementation, could you show a case it may happen? Second, this works, but not with UPDATE, see the updated fiddle (before the last command) –  MatheusOl Sep 13 '13 at 5:23
I'm Sorry MatheusOl for misunderstood. Jump means, in the list ({1},{1.1},{1.2}). If I try to insert chpath(1,4), either insert as {1.3} or don't allow if not in order. Second one, got it. Really nice... thanks lot !!! –  gnabhan Sep 13 '13 at 5:42

First, lets try to generate the chnum column using a recursive query (without using the generated, of course):

        SELECT c.id, c.parentid, ARRAY[row_number() OVER(ORDER BY c.id)] AS chnum
        FROM chapters c
        WHERE c.parentid IS NULL
        UNION ALL
        SELECT c.id, c.parentid, array_append(r.chnum, row_number() OVER(PARTITION BY c.parentid ORDER BY c.id)) AS chnum
        FROM chapters c JOIN r ON c.parentid = r.id
SELECT r.id, array_to_string(r.chnum, '.') AS chnum, parentid FROM r
ORDER BY r.chnum;

Now, we just need to take this result and update every id that doesn't match the chnum:

        SELECT c.id, c.parentid, ARRAY[row_number() OVER(ORDER BY c.id)] AS chnum
        FROM chapters c
        WHERE c.parentid IS NULL
        UNION ALL
        SELECT c.id, c.parentid, array_append(r.chnum, row_number() OVER(PARTITION BY c.parentid ORDER BY c.id)) AS chnum
        FROM chapters c JOIN r ON c.parentid = r.id
UPDATE chapters c
SET chnum = array_to_string(r.chnum, '.')
WHERE c.chnum IS DISTINCT FROM array_to_string(r.chnum, '.') AND c.id = r.id;

See this SQL Fiddle that I used to test it.

I'm making some assumptions here:

  1. The id column is primary key.
  2. The order is defined by the id column, probably a pos column or after/before thing would be better.

Also, unless it is a really huge table, I wouldn't store the chnum result, instead I'd create a view with the first query and just use it on the code.

share|improve this answer
Your second assumption is wrong (if the data in the questions are). Check the ID 7. Nevertheless, I think your idea could be adapted to fit this, just first the parents should be updated where the chnum is equal or greater to the one to be inserted. –  dezso Sep 11 '13 at 11:19
@dezso, I know the second assumption is wrong, but the OP didn't say what define the order, I sent a example that the OP should adapt for its case or explain better here (for us to improve the answer). About the update, I agree, that is a good idea to add this filter on recursive clause if used on a trigger, but again, we need to know what defines the order to do this. –  MatheusOl Sep 11 '13 at 11:23
I think the order is defined by chnum itself (see the position of the inserted row). –  dezso Sep 11 '13 at 11:32
thanks @MatheusOl. My case, chnum dynamically change. How I could insert new row inside list. –  gnabhan Sep 12 '13 at 0:55
@gnabhan: as dezso pointed, seems the problem is harder then I thought... So, using my approach you should use an auxiliary column to define the order... I have an idea without this need, let me elaborate it and put another answer... –  MatheusOl Sep 12 '13 at 1:31

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.