CODEWITHSUNDEEP
sqlpostgresqlrecursionpostgresql-9.4
Dmitry Volkov
Dmitry Volkov

Reputation: 1337

Iterating over the table with recursive query

I have the following data:

cte1
=================
gp_id | m_ids
------|----------
1     | {123}
2     | {432,222}
3     | {123,222}

And a function foobar(m_ids integer[]). The function contains the following cte:

with RECURSIVE foo as (
    select id, p_id, name from bar where id = any(m_ids)
    union all
    select b.id, b.p_id, b.name from bar b join foo f on f.p_id = b.id
)

The function is being used kind of like this: 

select foobar(m_ids) from cte1;

Now, as a part of a process of improving performance, I was told to get rid of the function. My plan was to use cte foo in my cte chain, but I stuck trying to adjust usage of any(m_ids).

EDITED: To be clear, the problem is that m_ids that are used in the where id = any(m_ids) statement is the parameter of the function, so I got to transform cte in order to make it work outside of the function.

I thought of the following: 

with RECURSIVE foo as (
    select (select id, p_id, name from bar where id = any(cte1.m_ids)
    union all
    select b.id, b.p_id, b.name from bar b join foo f on f.p_id = b.id)
    from cte1
)

But that would not work because
1) recursive query "foo" does not have the form non-recursive-term UNION [ALL] recursive-term
2) subquery must return only one column

In the end, I would like to get my data in the form like this:

m_ids    |foobar_result 
---------|-------------
{123}    | 125        
{432,222}| 215

Upvotes: 0

Views: 1177

Answers (1)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

Maybe JOIN that table holding parameter?

with RECURSIVE foo as (
    select m_ids, id, p_id, name from bar
    JOIN cte1 ON id = ANY(m_ids)
    union all
    select m_ids, b.id, b.p_id, b.name from bar b join foo f on f.p_id = b.id
)

Upvotes: 1

Related Questions