using a values returned from a function in another query inside a function

Question

I am trying the construct a Postgres function which can use the result of another select in where clause. The following works fine

create or replace function get_film_count(a varchar[], v varchar[])
 RETURNS Table(costs decimal, id varchar, in_node varchar[], out_node varchar[], cycle BOOLEAN,visted_list varchar[])
 language plpgsql
 as
 '
 begin
   RETURN QUERY  SELECT  edg.costs, edg.id, ARRAY[edg.in_node], ARRAY[edg.out_node], edg.in_node = ANY(v), v || ARRAY[edg.in_node]
        FROM edges edg where edg.in_node=ANY(a) AND edg.in_node != ANY(v)
        union all 
            SELECT  edg.costs, edg.id, ARRAY[edg.in_node], ARRAY[edg.out_node], edg.in_node = ANY(v), v || ARRAY[edg.in_node]
        FROM edges edg where edg.in_node=ANY(SELECT edg.out_node
        FROM edges edg where edg.in_node=ANY(a) AND edg.in_node != ANY(v));
 end;
';
select * from get_film_count(ARRAY['a'], ARRAY['']);

However I am looking for something similar to the following

create or replace function get_film_count(a varchar[], v varchar[])
 RETURNS Table(costs decimal, id varchar, in_node varchar[], out_node varchar[], cycle BOOLEAN,visted_list varchar[])
 language plpgsql
 as
 '
 begin
   list_of_out_nodes= select out_node from (get_film_count(ARRAY(edg.out_node), v || ARRAY[edg.in_node]))
   RETURN QUERY  SELECT  edg.costs, edg.id, ARRAY[edg.in_node], ARRAY[edg.out_node], edg.in_node = ANY(v), v || ARRAY[edg.in_node]
        FROM edges edg where edg.in_node=ANY(a) AND edg.in_node != ANY(v)
        union all 
            SELECT  edg.costs, edg.id, ARRAY[edg.in_node], ARRAY[edg.out_node], edg.in_node = ANY(v), v || ARRAY[edg.in_node]
        FROM edges edg where edg.in_node=ANY(list_of_out_nodes);
 end;
';
select * from get_film_count(ARRAY['a'], ARRAY['']);

So basically I am looking for using calling the function recursively instead and get the value of a column as list and feed it in the RETURN QUERY Note: I am aware of recursive functionality of Postgres but I am specifically looking for this approach

Answer 1

I believe you are going to need a CTE to allow the set returned by the first function call to be usable in the second call, something like this:

CREATE OR REPLACE FUNCTION get_film_count(a varchar[], v varchar[])
RETURNS TABLE(costs decimal, id varchar, in_node varchar[], out_node varchar[], cycle BOOLEAN, visited_list varchar[])
LANGUAGE plpgsql
AS $$
DECLARE
    list_of_out_nodes varchar[];
BEGIN
    RETURN QUERY  
        WITH recursive_nodes AS (
            SELECT edg.out_node 
            FROM get_film_count(ARRAY[edg.out_node], v || ARRAY[edg.in_node])
        )
        SELECT edg.costs, edg.id, ARRAY[edg.in_node], ARRAY[edg.out_node], edg.in_node = ANY(v), v || ARRAY[edg.in_node]
        FROM edges edg 
        WHERE edg.in_node = ANY(a) AND edg.in_node != ANY(v)
        UNION ALL 
        SELECT edg.costs, edg.id, ARRAY[edg.in_node], ARRAY[edg.out_node], edg.in_node = ANY(v), v || ARRAY[edg.in_node]
        FROM edges edg 
        WHERE edg.in_node = ANY((SELECT * FROM recursive_nodes));
END; $$;

---

Can't say I'm over-the-moon keen on the concept however. Seems unduly "smart" and may be a support problem (this is just gut feel!).