Writing a PostgreSQL Function That Coalesces Two Other Functions' Results

TLDR; I want to make a PostgreSQL function that returns the result of function A (find_recommendation_in_group) IF it returns anything ... or the result of function B (find_child_recommendation_of_group) if it doesn't ... ie.:

COALESCE(
  find_recommendation_in_group(),
  find_child_recommendation_of_group()
);

However, I'm struggling with PostgreSQL issues related to COALESCE.

Issue Details: I have data organized into a hierarchical structure, where a "recommendation" can be direct member of a "group" (ie. a child recommendation), or it could be a member of a group that's a member of another group (ie. a grandchild recommendation), and so on.

I want to create a PostgreSQL function (find_recommendation_in_group) to find a recommendation in a group, no matter how deeply nested it is in other groups. To help I built two functions, find_child_recommendation_of_group (finds direct children) and find_grandchild_recommendation_of_group (currently just finds grandchildren, but I plan to make it recursive to find great-grandchildren and such).

I naively defined my function as:

CREATE OR REPLACE FUNCTION find_recommendation_in_group (group_id integer, title varchar)
RETURNS TABLE (LIKE recommendations) AS $$

  SELECT COALESCE(
      find_child_recommendation_of_group(group_id, title),
      find_grandchild_recommendation_of_group(group_id, title)
  )

$$
LANGUAGE sql VOLATILE STRICT SECURITY DEFINER;

But I got the error:

set-returning functions are not allowed in COALESCE

A different Stack Overflow answer suggested I switch to a LATERAL

CREATE OR REPLACE FUNCTION find_recommendation_in_group (
  group_id integer, title varchar
)
RETURNS TABLE (LIKE recommendations) AS $$
  SELECT t.*
      FROM (VALUES (1), (NULL)) AS t(value)
      JOIN LATERAL COALESCE(
          find_child_recommendation_of_group(
              group_id, system_title, edition_title
          ), 
          find_grandchild_recommendation_of_group(
              group_id, system_title, edition_title
          )
      ) AS result ON true;
$$
LANGUAGE sql VOLATILE STRICT SECURITY DEFINER;

But I still got the same error:

set-returning functions are not allowed in COALESCE

Can anyone explain how I can write a function that returns the COALESCE-d results of two other functions. I'm not wedded to COALESCE, so if there's another way (eg. with an IF) that'd work also.

Solution

See simple example
If first query in function not return result FOUND variable is false.
In this case run second query.

(the special variable FOUND is set to true if the query produced at least one row, or false if it produced no rows (see Section 41.5.5(https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS))

create table recommendations (group_id int, title varchar(100),parent_id int);
insert into recommendations values
 (1,'Group-1',null)
,(2,'Group-2',1)
;

group_id	title	parent_id
1	Group-1	null
2	Group-2	1

-- example  2 queries in function -- LANGUAGE plpgsql
CREATE OR REPLACE FUNCTION recommendation_of_group (p_group_id integer, title varchar)
RETURNS TABLE (LIKE recommendations) AS $$
  BEGIN
    RETURN QUERY select * from recommendations rc where rc.group_id=p_group_id;

    IF NOT FOUND THEN
        return query select * from recommendations rc where rc.parent_id=p_group_id;
    END IF;

    RETURN;
 END;
 
$$
LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;

First example. Rows returned by first query.

select * from recommendation_of_group (1,'xx');

group_id	title	parent_id
1	Group-1	null

Next example. Rows returned by second query.

select * from recommendation_of_group (2,'xx');

group_id	title	parent_id
2	Group-2	1

fiddle