Inserting into array on SELECT at index in Postgres

Question

I'm using a Postgres CTE to recurse through a parent-child tree. The following script will recurse from root to leaf and append to the end of path (ARRAY).

WITH RECURSIVE tree AS (
  // get roots
  SELECT entity_id, parent_id, ARRAY[entity_id] as path
     FROM entity
     WHERE parent_id is null

  UNION ALL

  // recursive step
  SELECT c.entity_id, c.parent_id, path || c.entity_id
     FROM tree t
     JOIN entity c ON c.parent_id = t.entity_id
)

SELECT path 
  FROM tree t 
  WHERE entity_id NOT IN (SELECT DISTINCT parent_id FROM tree WHERE parent_id IS NOT NULL);

Instead of appending to the end of the path at each step, I would like to insert into the array by a index column. Is it possible to do this within SELECT?

Hypothetical Solution

SELECT  path[c.index] = c.entity_id
   FROM tree t
   JOIN entity c ON c.parent_id = t.entity_id

Expected output

| entity_id  | index       | parent_id |
|:-----------|------------:|:----------|
| a          |          3  | d         |
| b          |          5  | a         |
| c          |          1  | (none)    |
| d          |          2  | c         |

path = [c,d,a,(none),b]

Answer 1

The function implements the assignment arr[idx]:= elem and returns arr. If necessary the array is automatically expanded to accommodate the new element.

create or replace function array_set_element(arr text[], elem text, idx int)
returns text[] language plpgsql as $$
begin
    if cardinality(arr) < idx then
        arr:= arr || array_fill(null::text, array[idx- cardinality(arr)]);
    end if;
    arr[idx]:= elem;
    return arr;
end $$;

Example:

select array_set_element('{a, b}'::text[], 'e', 5);

 array_set_element 
------------------------
 {a,b,NULL,NULL,e}
(1 row) 

Use the function in your query:

WITH RECURSIVE tree AS (
  SELECT entity_id, parent_id, array_set_element('{}'::text[], entity_id, index) as path
     FROM entity
     WHERE parent_id is null

  UNION ALL

  SELECT c.entity_id, c.parent_id, array_set_element(path, c.entity_id, c.index)
     FROM tree t
     JOIN entity c ON c.parent_id = t.entity_id
)

SELECT path 
FROM tree t 
WHERE entity_id NOT IN (SELECT DISTINCT parent_id FROM tree WHERE parent_id IS NOT NULL);

      path      
----------------
 {c,d,a,NULL,b}
(1 row)