I seek metrics on complete manifolds whose scalar curvature represents a singularity of the form $\frac{h}{\rho^2}$ where $h$ is a continuous function and $\rho$ vanishes on the boundary of some compact set of the manifold.

Take the graph $t=\sqrt{x^2+y^2+z^2}$ with induced intrinsic metric. In $(x,y,z)$coordinates, the scalar curvature is $$\frac C{x^2+y^2+z^2}.$$ Are you happy? 


There is a description a plane curve called its Cesàro equation, in which the curvature is given as a function $k$ of arc length $s$. Suppose that $k$'s domain is $(0,1]$. Then it should be clear that, given one point and a tangent vector at that point, the corresponding curve $\mathbf{r}(s)$ exists and is unique for $s\in[0,1]$. (Even if the function $k$ misbehaves at $s=0$, the limit $\mathbf{r}(0)$ exists.) Define the curve C whose Cesàro equation for $s\in(0,1]$ is $k=1/s^2$, with (arbitrarily, but for concreteness) $\mathbf{r}(0)=(1,0)$, and its tangent vector upward at $s=0$. Form a surface of revolution S by revolving C about the $y$ axis. S is compact, and the locus $s=0$ is a unit circle forming a boundary of S. The Gaussian curvature $\kappa$ is equal in magnitude to the product of the curvatures along the two principal axes. The curvature along the azimuthal axis is 1 for $s=0$, so $\kappa\sim 1/s^2$ as $s\rightarrow 0$. 


You can use the warped product of two Riemannian manifolds, with a warping function which vanishes on a compact region. Let $(B,g_B)$ and $(F,g_F)$ be two Riemannian manifolds, and $f$ a smooth function on $B$. The warped product of $B$ and $F$ with warping function $f$ is the manifold $$B\times_f F:=\big(B\times F, \pi^*_B(g_B) + (f\circ \pi_B)\pi^*_F(g_F)\big),$$ where $\pi_B:B\times F \to B$ and $\pi_F: B \times F \to F$ are the canonical projections. It is customary to call $B$ the base and $F$ the fiber of the warped product $B\times_f F$. Let $B \times_f F$ be a warped product, with $\dim F>1$. Then, the scalar curvature $s$ of $B \times_f F$ is related to the scalar curvatures $s_B$ and $s_F$ of $B$ and $F$ by $$s = s_B + \frac {s_F}{f^2} + 2\dim F\frac{\Delta f}{f} + \dim F(\dim F  1)\frac{\langle grad f, grad f\rangle_B}{f^2}.$$ You can play with this formula to obtain what you are looking for  by trying to make $s_B + 2\dim F\frac{\Delta f}{f}$ vanish, or at least to be of the form $\frac{h}{f^2}$. To make it vanish, you may solve the equation $$\Delta f+\frac{s_B}{2\dim F}f=0.$$ More about warped products in O'Neill's "SemiRiemannian geometry: with applications to relativity", and in this paper. 

