In today's budget-strapped schools, there is usually only one psychologist per district. The psychologist’s job is basically to test students for learning disabilities to see if they need an IEP (Individualized Education Program). Unfortunately, the psychologist does not really treat anything and does very little follow up. He or she simply tests the student, usually in a couple of hours, and then makes a recommendation. School site special education teachers proceed from there to design the student's modifications. However, that does not mean it is not an important function. The psychologist is needed to make sure the child gets the needed interventions.
To learn more about IEPs, read here:
School psychologists can counsel students (although it's difficult because they have so many schools to cover), but their primary function is to evaluate students for things like learning problems. This process is intended to determine who is eligible to receive special services in school to help them succeed.
School psychologists can also perform the very important role of educating classroom teachers on how to deal with students who suffer from emotional stress, illness, or other difficulties. In this role, they often serve on IEP committees and lead faculty development sessions.
Depends on what level of school you're talking about. At the grade school level, psychologists are very involved in helping kids deal with social situations. They teach kids things like how to make friends and how to deal with peer pressure. They also give one on one help to kids who have specific issues that affect them.