PostgreSQL: apply return of query to a function

I have a recursive method defined below:

with recursive temp(id, s, r, e) as (
    select *
    from rel
    where rel_to_id = <parameter from sql query>

    union all

    select *
    from temp P
    inner join relationship C on P.r = C.s
)

and I need to call this on each row returned from an SQL query with a column value defined in the开发者_如何学运维 recursive query (marked as )

I dont really want to call X queries through python which slows things down, there must be a way to do it in sql. I tried to write a function in plpgsql but I am having trouble defining the return type setof TABLE and taking the union of it each time.

I'm not sure I fully understand the problem, is your issue to call your recursive function on more than 1 initial value, as per the values returned by your ?

In that case, could you just create your initial table at once with all required values, and then recurse through it? Something like this:

with recursive temp(id, s, r, e) as (
    select *
    from rel r
    join <sql query> q on r.rel_to_id = q.id

    union all

    select *
    from temp P
    inner join relationship C on P.r = C.s
)

IMHO calling the recursive query multiple times, once per parameter value is the worst you can do. Instead you should combine the recursive query with the one that supplies the parameter values and iterate over their "product". Often it is convenient to temporarily pack the recursive query into a view and join that with the other leg of the query. The optimiser will take care.

CREATE VIEW temp_view AS (
    with recursive temp(id, s, r, e) as (
    SELECT *
    from rel
    WHERE {recursive_condition}
    -- Omit the restriction
    -- AND rel_to_id = <parameter from sql query>
    union /* all? */
    select *
    from temp P
    inner join relationship C on P.r = C.s
    WHERE {recursion_stopper}
    ) SELECT * FROM temp_view
);

-- Now join the recursive part
-- with the one that supplies the restrictions
SELECT ...
FROM temp_view tv
    , other_table ot
WHERE tv.parameter = ot.parameter
AND ... -- more stuff
;