organization capacity

add_organization_membership.sql

-- Function: add_organization_membership(integer, integer, boolean)

-- DROP FUNCTION add_organization_membership(integer, integer, boolean);

CREATE OR REPLACE FUNCTION add_organization_membership(
    _organization_id integer,
    _user_id integer,
    _is_mananger boolean)
  RETURNS boolean AS
$BODY$
  DECLARE
    existing_users_count integer;
    organization_capacity integer;
    member_exists boolean = false;
    success boolean = false;
  BEGIN
    -- check existence
    SELECT EXISTS (SELECT TRUE FROM auth_organization_memberships aom WHERE (aom.organization_id = $1 AND aom.user_id = $2)) INTO member_exists;
    IF member_exists THEN
    -- if exists, update without capacity check
    UPDATE auth_organization_memberships SET is_manager = $3 WHERE (organization_id = $1 AND user_id = $2);
    SELECT TRUE INTO success;
    ELSE
    -- count existing users
    SELECT COUNT(*) INTO existing_users_count FROM auth_organization_memberships aom WHERE (aom.organization_id = $1);
    -- count capacity
    SELECT max_users INTO organization_capacity FROM auth_organizations o WHERE (o.organization_id = $1);
    -- if capacity is not exceeded
    IF existing_users_count < organization_capacity THEN
        INSERT INTO auth_organization_memberships(organization_id, user_id, is_manager) VALUES ($1, $2, $3);
    END IF;
    END IF;
    RETURN success;
  END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION add_organization_membership(integer, integer, boolean)
  OWNER TO postgres;