0

What is the best approach to parameterize a recursive query?

As an example, let's take the following query: (taken from postgresqltutorial):

WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = 2
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) 
SELECT *
FROM subordinates;

How could I modify it to allowed me to pass employee_id as a parameter or clause of my query?

I found a solution where I put this query in a function that receives employee_id as parameter and return a table (with employee_id, manager_id, full_name columns), but I'm not sure if this is a good/elegant solution.

CREATE OR REPLACE FUNCTION func_subordinates(employee_id_param INT)
RETURNS TABLE (
    employee_id INT,
    full_name VARCHAR,
    manager_id INT) AS
$BODY$
BEGIN
    RETURN QUERY

    WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = employee_id_param
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) 
SELECT *
FROM subordinates;

IF NOT FOUND THEN
    RAISE EXCEPTION 'No found %.', $1;
END IF;

RETURN;
END;
$BODY$
LANGUAGE plpgsql;

Could I get the same result using a VIEW where employee_id could be passed as a clause or something like that?

Any suggestion will really appreciated.

6
  • 2
    Offtopic: Are you sure about the EXCEPTION? Not finding any result is imho not an exception, it's normal behaviour for a database when there is no data that matches your request. Commented Apr 2, 2022 at 10:20
  • @FrankHeikens, it really depends the case. It is just a bit of business logic inside this function. But I agree with you that in the majority of the cases it should not be an exception. Commented Apr 2, 2022 at 10:40
  • 3
    Views can't have parameters. Using a function is the only option you have here as far as I can tell. Commented Apr 2, 2022 at 11:20
  • BTW: the function could be made LANGUAGE sql; Commented Apr 2, 2022 at 12:26
  • 1
    You do it the same way you would if it were not recursive. Commented Apr 2, 2022 at 14:50

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.