xCloudx8 xCloudx8 - 2 months ago 14
SQL Question

Fill empty space on recursive SQL with last value

This is the SQL query i used to get data from my table which contains parent-child values:

WITH RECURSIVE cte AS
(
SELECT array[r.term1_id, r.term2_id] AS path
FROM temp_table r
LEFT JOIN temp_table r0 ON r0.term1_id = r.term2_id
WHERE r0.term1_id IS NULL
UNION ALL
SELECT r.term1_id || c.path
FROM cte c
JOIN temp_table r ON r.term2_id = c.path[1]
)
SELECT path
FROM cte
ORDER BY path;


The result is something like this:

"{1,5,6,1452}"
"{1,5,6,1470,1475}"


How can i fill the last space of the first array obtaining something like this?

"{1,5,6,1452,1452}"
"{1,5,6,1470,1475}"


So i want to repeat the last non null value until i cover every level.




ANSWER:

Here's a functioning statement

CREATE OR REPLACE FUNCTION fill_with_last_element(arr anyarray, n INTEGER)
RETURNS anyarray LANGUAGE plpgsql as $$
DECLARE
l int = array_length(arr,1);
BEGIN
RETURN CASE
WHEN l > n THEN arr
ELSE arr || array_fill(arr[l], array[n- l])
END;
END $$;

WITH RECURSIVE cte(path) AS (
SELECT array[r.term1_id, r.term2_id] AS path
FROM temp_table r
LEFT JOIN temp_table r0 ON r0.term1_id = r.term2_id
WHERE r0.term1_id IS NULL
UNION ALL
SELECT r.term1_id || c.path
FROM cte c
JOIN temp_table r ON r.term2_id = c.path[1]
),
max_len AS (
SELECT max(array_length(path, 1)) max_len
FROM cte
)
SELECT fill_with_last_element(path, max_len)
FROM cte
CROSS JOIN max_len
ORDER BY path;

Answer

You cannot generate arrays with equal lengths because you do not know the length before the query terminates. You should modify the results. This function will be helpful:

create or replace function fill_with_last_element(arr anyarray, n integer)
returns anyarray language plpgsql as $$
declare
    l int = array_length(arr,1);
begin
    return case 
        when l > n then arr 
        else arr || array_fill(arr[l], array[n- l]) 
    end;
end $$;

Example:

with cte(path) as (
    values
    ('{1,5,6,1452}'::int[]),
    ('{1,5,6,1470,1475}')
),
max_len as (
    select max(array_length(path, 1)) max_len
    from cte
    )
select fill_with_last_element(path, max_len)
from cte
cross join max_len

 fill_with_last_element 
------------------------
 {1,5,6,1452,1452}
 {1,5,6,1470,1475}
(2 rows)
Comments