Let $X$ be a set. Let $\mathrm{Over}(X)$ be the category of sets $A$ with a map $A\to X$; the morphisms are equivariant maps, that is, maps which make the obvious triangle commute.
A map of sets $f\colon X\to Y$ induces a functor $f_*\colon\mathrm{Over}(X)\to\mathrm{Over}(Y)$ by postcomposition. This shows that $\mathrm{Over}$ is a functor from the category of sets to the category of categories (up to usual set theoretic issues which I would like to ignore).
Now let $X=X_1\sqcup X_2$ be a disjoint union, i.e., a coproduct of sets. Every set $\phi\colon A\to X$ gives a pair consisting of a set over $X_1$ and a set over $X_2$. These are the restrictions $\phi^{-1}(X_i)\to X_i$. On the other hand, if you give me a set over $X_1$ and a set over $X_2$, then I can write down a corresponding set over $X$ by just taking the disjoint union.
I think what I just said means that the coproduct decomposition $X=X_1\coprod X_2$ gives a product decomposition of categories $\mathrm{Over}(X)\cong \mathrm{Over}(X_1)\times \mathrm{Over}(X_2)$.
I am confused by the apparent fact that the covariant functor $\mathrm{Over}$ turns coproducts into products. What is happening here?
Edit: As Theo explains in his comment below, the coproduct of categories is just the disjoint union. So it is not a very complicated thing, but also not what we want here—which is really pairs of objects.
Edit: Does the pullback diagram
$$\begin{array}{ccc}
P & \rightarrow & A\\
\downarrow_{f^!(\phi)} & & \downarrow_\phi\\
X & \rightarrow^f & Y
\end{array}$$
define a wrong-way functoriality for maps $f\colon X\to Y$ which would turn the canonical maps of a coproduct $X_1\sqcup X_2$ to the canonical maps of the product $\mathrm{Over}(X_1)\times \mathrm{Over}(X_2)$? This seems to correspond to Omar's observation.
Best Answer
The functor Over is covariant, and you do have that Over(X1∐X2)≅Over(X1)×Over(X2). However, I wouldn't say Over "turns coproducts into products", since that phrase usually means that additionally the canonical maps Xi→X1∐X2 are sent to the canonical projections Over(X1)×Over(X2)→Over(Xi), and for that, of course, the functor would need to be contravariant.
In this example what is really going on is that Over(X) is equivalent to Fun(X,Set), the category of functors from X to Set (where the set X is regarded as a discrete category, i.e., the category whose objects are the elements of X and that only has identity morphisms). [The equivalence is given by sending f:A→X to the functor that sends an element x in X to its inverse image under f.] Of course, the functor Set→Cat given by Fun(_,Set) is contravariant, and it does turn coproducts into products in the sense I mentioned above. In summary, the covariant functor Over and the contravariant functor Fun(_, Set) agree on objects even though they have opposite variance, and the second turns coproducts into products.
Edit to answer the edit to the question: the pullback does indeed make Over(X) into a contravariant functor which is naturally isomorphic to Fun(_,Set). This is easy to check: if you have an object of Over(Y), say f:A→Y, and a function g:X→Y, then the inverse image of an element x in X under the pullback object of Over(X) is just the preimage of g(x) under f (the definition of pull-back is basically chosen to make this true).