Writing recursive SQL query that calls function (postgreSQL)

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?

Continue reading...