test

CREATE OR REPLACE FUNCTION test(uuidin text)
RETURNS TABLE(uuid integer, name text, business_account_id integer, upline_id integer, user_username text, grade_name text, grade_background_image text) AS $BODY$
BEGIN
   RETURN QUERY
   WITH RECURSIVE subordinates AS (SELECT b.uuid , b.name, b.business_account_id, b.upline_id,
   b.user_username, g.name AS grade_name, g.background_image AS grade_background_image, (SELECT SUM(amount) FROM user_pin_transactions WHERE business_account_uuid = b.uuid) AS omzet FROM business_accounts b
   INNER JOIN grades g ON id = b.grade_id
   WHERE b.uuid = uuidin
   UNION SELECT db.uuid, db.name, db.business_account_id, db.upline_id, db.user_username, dg.name AS grade_name, dg.background_image AS grade_background_image, 
   (SELECT SUM(amount) FROM user_pin_transactions WHERE business_account_uuid = db.uuid) AS omzet 
   FROM business_accounts db
   INNER JOIN grades dg ON id = db.grade_id  
   INNER JOIN subordinates s ON s.business_account_id = db.upline_id) SELECT *, (SELECT SUM(omzet) FROM subordinates) AS omzet_group FROM subordinates;
END;
$BODY$ LANGUAGE plpgsql;