I know that the iPhone supports video out using the AV cable accessory that Apple sells. This would indicate the possibility of an app which can use said cable to display video.
Does anything like this exist? If it isn't possible, why not?
There are no known solutions for video input to the iPhone (other than the built-in camera of course). The AV cable you mentioned only provides video output and since there are no pins for video input on the dock connector (although the pinouts have often changed during the history of the dock connector), and the iPhone has no other inputs suitable for video input, this is very unlikely to happen with current iPhone/Pod/Pad models.
The closest solution I can think of would be to use an external recording device (a computer with a video capture device for example) and stream the resulting video the the iPhone over Wifi.
There may be another way to do this. NTSC Composite Video is the quadrature amplitude modulated mix of the amplitude/brightness/intensity and color portions of the video signal, with two interleaved images being delivered 30 times per second. (525 lines total) it might be possible to sample the analog signal at 48khz and DSP that into a useable digital video - although possibly at reduced size (1/4 VGA) and frame rates (15 FPS) which could then be delivered via the iPhone headphone/mic jack or audio lines in the 30 pin connector.