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]

Please Edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. edit your question - do not post code or additional information in comments. — user330315
– user330315, Commented Sep 14, 2018 at 16:09
I don't understand where the index comes from? The expression ` path[c.index] = path[entity_id]` is certainly valid if c.index and entity_id are both integer values — user330315
– user330315, Commented Sep 14, 2018 at 16:15
Corrected to path[c.index] = c.entity_id. That expression returns a boolean which throws error on union — Brendan Frick
– Brendan Frick, Commented Sep 14, 2018 at 16:25

klin · Accepted Answer · 2018-09-14 18:25:33Z

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)

Thanks this worked as intended! Replaced cardinality(arr) with array_length(arr,1) for version < 9.4

Collectives™ on Stack Overflow

Inserting into array on SELECT at index in Postgres

Hypothetical Solution

Expected output

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

Hypothetical Solution

Expected output

1 Answer 1

1 Comment

Related