validate_user.sql

CREATE OR REPLACE FUNCTION validate_user(
    email    TEXT
  , token    UUID
)
  RETURNS BOOL
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
  IF exists(SELECT
              1
            FROM tokens
            WHERE tokens.email = validate_user.username
                  AND tokens.token = validate_user.token
                  AND token_type = 'validation')
  THEN
    UPDATE application.users
    SET verified = TRUE
    WHERE users.email = validate_user.email;

    DELETE FROM tokens
    WHERE tokens.email = validate_user.email
          AND tokens.token = reset_password.token
          AND token_type = 'validation';
  ELSE
    RAISE EXCEPTION
    USING MESSAGE = 'Invalid user or token';
  END IF;
END;
$$;