There are red, green, and yellow curry pastes. What is the purpose for each of these pastes and which is the hottest?

If the query is about Thai pastes, they are usually combined with coconut milk (and occasionally stock) to form a curry.
Yellow is the mildest color, and it contains turmeric, which gives it its characteristic hue. Vegetarian yellow curries are common.