In Video Game RPGs, it usually depends on the game. For instance, in fallout, which is heavily based off of the real world, and has the same gods/etc, I play my character as nonreligious, as I am a nonreligious fellow in real life and it is easiest for me to relate to a nonreligious character in a setting similar to the real world.
In a game like Neverwinter Nights, Icewind Dale, or BG, I can go both ways. It seems a bit odd to be a cleric of, say, Talos, considering the character's blood. One would think that an evil god like Talos would try to manipulate the (spoiler ahead) child of Baal, but it never really comes up. In general though, whatever god you worship in D&D games, it rarely seems to have an effect on anything. Just for fun, I made a Drow Cleric of Lloth in Neverwinter Nights II, just to see if it had an effect. Unfortunately, no-one seemed to care, including the PCs wood elf adoptive father. One would think that would be an awkward relationship.
In tabletop RPGs, I don't play characters that are religious, because I am always stuck with the role of the DM.