Lo and behold, someone actually built exactly that.
The game is called dream:scape and it's by "Speedbump Studios" (which is really just one guy with some help from a few friends) and was written using the Unreal engine. It is, for all intents and purposes, the exact game I've been waiting for.
In dream:scape you play the role of Wilson. Wilson is an old man in a coma, in the moments just prior to death. And in the game world, it means he's entered his, well... dream:scape. The point of the game is to walk through Wilson's memories and reconstruct the important moments of his life - and solve a mystery from his youth that he's blocked out over all these years. You explore this coma-dream, effectively solving puzzles as simple as "find item, use item" - but as you do, more of the story is told via haunting voice overs and unlocked diary entries.
Sadly, it's not a super long game, but as it only costs $1.99 you do get your money's worth.
It's not a perfect experience, but it's still a good one. The game's story is slightly cliched, but still compelling. The visuals are pretty solid, but not quite as good as those in other Unreal engine worlds like Castlerama and Epic Citadel/Infinity Blade. Frankly the "spider" near the end of the game is a pain in the ass. On the other hand, the voice acting and presentation are spot on. The game manages to be eerie and dreamlike in ways I didn't think were possible.
I mentioned a spider in that last paragraph, and let me tell you -- that spider is a f***ing b****. The game has a couple of sequences where to defeat the "guardians of memory" you have to perform specific swipe actions on the screen. Now if you fail, the game doesn't end - it merely bounces you back a little. The problem is the sensitivity isn't really all that good on these sequences, and while most guardians have simple commands and are easy to beat, the damned spider... geh.
I found the only way I could beat it was to use my stylus (which I use for my artwork), as it provided a bit more precision.
The other major problem is one all Unreal games seem to have: they're memory intensive. Now, if you have an iPad 2 or an iPhone 4, this isn't a problem. But if you have a 3GS, iPad 1 or even the most recent iPod touch, you have only half the RAM. This means that after you install the game, you need to reboot on those latter devices. It also means that players on those lower memory devices need to quit all multitasking apps before running the game.
I know I've highlighted some bad points, but let me be clear: this is the best video game I've played in a long time. I love this game.
There is a "Lite" version in the app store you can try if you want to see if you like it. It cuts the story off right as you'd go to the bluffs (although it doesn't TELL you that you've taken the game as far as you can), but it should give you a taste as to whether or not you'll like the game.