Recursive update for tree structure in PostgreSQL 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:
First, lets try to generate the chnum column using a recursive query (without using the generated, of course):

WITH RECURSIVE r AS (
        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:

WITH RECURSIVE r AS (
        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, '.')
FROM r
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:


The id column is primary key.
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.