Is it possible to make a node setup to where if a camera is close to an object, the texture gets bigger, but far away it decreases in size?
It is. The crucial point is using the cycles Camera Data node, from then on should be trivial.
Just use the View Distance output socket to transform the texture coordinates as you see fit.
In my setup I just use a Color Mix node to mix between two Texture Mapping vector coordinates with different scale factors, and the View Distance as mixing factor, but you can get inventive with the distance value and use it as you see fit.