My reflex response is nothing happens.
Great!
If the player wanted a clerics powers then he should have chosen to run a cleric.
I think that gets at the issue I was probing. If we say that cleric is a distinct niche and other people should not be able to have its powers, and that the choice to gain those powers or not is made in character creation, not at the table, then your answer is the right one.
Some people don't share those assumptions and run a more free-wheeling game where character building is not as important or strictly enforced.
Why is there no cleric with them - or no other source of healing? Even assuming all PC's were out of available healing rescources then the question arises why are they still out looking for trouble? If they simply found themselves in over their heads well, that's just part of D&D ain't it?
My scenario is admittedly vague on this. It is possible that the wife character was the only healer and is now dying so she can't heal herself. It is possible that the party has healers but they are unavailable for some reason, such as a split party. It is also possible that they have no healers or healing items, which is a dicey situation to be in, and may reflect the DM imposing adverse conditions or the players choosing to take that chance.
It also is unclear what this battle was and whether they could reasonably have been expecting a fight or not. I didn't want to write a novel, so this is a vague question that I've asked and you'll have to make some of your own assumptions about what is playing out here. I did not make clear the extent to which the players took unnecessary risks and the extent to which the DM imposed this situation on them, and if that affects what your decision would be, that's certainly reasonable.
Why would the player expect circumstances to suddenly change in this instance? You can look at it as a good time to pay off on the characters acts of piety but you can also look at it as an opportunity for the characters faith to be tested. Why are the games existing mechanics and consequences regarding death and healing suddenly insufficient NOW?
...
The piety and devotion by the PC in question is what we in the business call "roleplaying". I sort of thought the idea was that you do it because it's fun. It makes the character more interesting and appealing to not just the player but everyone at the table. Why would a player NOW expect some mechanical reward for it? Why would a DM NOW decide that THIS situation merits special rewards and considerations?
Maybe this rationale is not sufficient for granting the player the ability to do something that is not on his character sheet. If you want to stick with the RAW, that is the first answer I provided, and ostensibly the correct answer by the letter and intent of the rules (for some editions anyway).
As to reasons for the contrary, to why the DM would do something, there are a few I can think of.
One, sentimentality. DMs often cheat to avoid killing PCs, and I threw in that this was the fighter's wife to add to the sentimental quality. Obviously, if the fighter picked up the axe of the fallen orc and dropped down on his knees and begged the deity to identify any magic powers the sword might have, he does not get that prayer answered, because no one cares. But this situation was designed to be more urgent and important.
Two, the DM, as others have noted, is playing the deities, and may wish to do so in an interventionist way depending on their powers and goals and their interest in the PCs, depending on the nature of his campaign and the setting.
Three, the DM may wish to emphasize improvisational play, and reward good roleplaying ideas that happen at the gaming table while making character building less important. This is more the old school approach, to an extent, and feels somewhat unconventional in the context of modern D&D where the build is everything, but some people really want choices during the game to determine what happens.
I do like TerraDave's suggestion of a prayer mechanic where choosing to venerate a given deity would gain a PC some appropriate boon - but the time to institute that is not in the middle of the game.
In general, I agree that rewriting the rules at the table is a dicey proposition. But some DMs are very loose that way.