referer recursive 6

DROP FUNCTION IF EXISTS get_referers(param_user_id bigint, param_user_bm_id SMALLINT) cascade;
CREATE FUNCTION get_referers(param_user_id bigint, param_user_bm_id SMALLINT)
returns TABLE  (
        referer_id bigint,
        referer_level INTEGER
)
AS $end$
    DECLARE
        origin_referer_id bigint;
        origin_level INTEGER;
    BEGIN
--        SELECT user_bms.user_id, COUNT(*) % 5 + 1
--        FROM users, user_bms
--        WHERE
--            users.user_id=param_user_bm_id
--        AND
--            user_bms.user_id=users.referer_id
--        AND
--            user_bms.bm_id=param_user_bm_id
--        GROUP BY user_bms.user_id
--        INTO origin_referer_id, origin_level;

        SELECT users.referer_id
        FROM users
        WHERE users.user_id = param_user_id
        INTO origin_referer_id;

        SELECT COUNT(*) % 5 + 1
        FROM user_bms
        WHERE
            user_bms.user_id=origin_referer_id
        AND
            user_bms.bm_id=param_user_bm_id
        INTO origin_level;

        RETURN query WITH recursive tree AS (
            SELECT origin_referer_id AS referer_id, origin_level AS referer_level

            UNION

            SELECT
                users.referer_id AS referer_id,
                tree.referer_level::INTEGER - CASE WHEN user_bms.user_id IS NULL THEN 0 ELSE 1 END AS referer_level
            FROM users
            left join user_bms
                ON user_bms.user_id = users.referer_id AND user_bms.bm_id = param_user_bm_id
            join tree
                ON tree.referer_id = users.user_id  AND users.user_id != users.referer_id
            WHERE
                tree.referer_level - CASE WHEN user_bms.user_id IS NULL THEN 0 ELSE 1 END > 0
            )
            SELECT tree.referer_id, origin_level FROM tree ORDER BY tree.referer_level limit 1;
    END;
$end$
language plpgsql;


SELECT * FROM get_referers(1122::bigint, 1::SMALLINT);