Writing recursive SQL query that calls function (postgreSQL)

Question

I have a DB with tables that maintain information about users and other users that are allowed to approve tasks for those users. This approver could be the user's supervisor (maintained in a 1-many relationship of the users table), or another user explicitly given approval powers (maintained in a separate many-to-many relationship table).

My goal is to find the complete tree (or chain) of "approvees" for a given user (i.e., who that user is allowed to approve for, together with anyone in the approval chain of those approvees). Since this is not as simple as looking up WHERE u1.username = u2.supervisor due to the many-to-many relationship of the "explicit other approvers" described above, this is not as simple as the example given here: https://www.postgresqltutorial.com/postgresql-recursive-query/

For the non-recursive case, I've written up a function that allows me to get all users allowed to be approved by a certain user, that looks like this (it also does some other stuff like formatting the result based on information held in another table, but the core bit of it is the stuff on either side of the union in the subquery):

CREATE OR REPLACE FUNCTION public.get_user_approvees(username text)
 RETURNS TABLE(approvee_username text, approvee_name text, approver_username text)
 LANGUAGE plpgsql
AS $function$
#variable_conflict use_variable
    BEGIN
        return query
--          with the below subquery, select the username and get names from preferences for
--          the approvee
            select sq.approvee, up.first_name || ' ' || up.last_name, username as "name" from
            (
--              get the approvees of the users group as a subquery
                select u2.username as approvee from group_approvers ga
                inner join users u2 on u2.group_id = ga.group_id
                where ga.approver = username
                and u2.username != username
                and u2.is_active 
                union 
--              add any other users this user is directly responsible for
                select ua.approvee from user_approvers ua
                inner join users u on u.username = ua.approvee 
                where ua.approver = username
                and u.is_active 
            ) as sq
            inner join users u on sq.approvee = u.username
            inner join user_preferences up on u.user_prefs = up.id; 
    END;
$function$
;

I thought that based on this, I should be able to pretty simply write a function that does the same thing, but recursively. My attempt does not work however, and I'm wondering (1) Why? and (2) How can I accomplish this?

Here's my attempt for the function with a recursive CTE:

CREATE OR REPLACE FUNCTION public.recursive_test(username text)
RETURNS TABLE(approvee_username text, approvee_name text, approver_name text)
LANGUAGE plpgsql
AS $function$
#variable_conflict use_variable
    BEGIN
        return query
            WITH RECURSIVE all_approvees AS (
                    (
                        SELECT * FROM get_user_approvees(username) 
                    )
                    UNION 
                    (
                        SELECT * FROM get_user_approvees(all_approvees.approvee)
                    )
            ) SELECT 
                *
            FROM all_approvees;
    END;
$function$
;

When I try to run this function, I get an error at runtime stating:

ERROR:  missing FROM-clause entry for table "all_approvees"
LINE 7:       SELECT * FROM get_user_approvees(all_approvees.approve...

Any thoughts?

Answer 1

This may not solve all of your problems, but you're getting that error simply because you don't have the recursive table in the FROM clause in the recursive part of your query. It should look something like this -

WITH RECURSIVE all_approvees (approvee, name) AS (
    SELECT * FROM get_user_approvees(username) 
    UNION 
    SELECT f.* FROM all_approvees, get_user_approvees(all_approvees.approvee) as f
) 
SELECT *
FROM all_approvees;