Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Add Comment
Please, Sign In to add comment