Vampire's Kiss
(1989)

Due to the obsessive idea of him becoming a vampire, Peter (Nicolas Cage) gradually descends into a spiral of madness and does some horrible acts, which include: eating a live cockroach from his kitchen stove (AND HE REALLY DID IT, BY THE WAY!!); raping his assistant Alva (Maria Conchita Alonso); and even biting some drunk girl at a club -fake teeth on and all-, only to throw up seconds later.  He also walks around in the streets with a wooden stack in his hand, begging for someone to kill him with it.

Emilio (Bob Lujan), Alva's brother, after learning about her being raped, goes to Peter's place with the intention of killing him in retaliation for this atrocity.  When he gets there, he finds Peter sheltering from daylight under an upside-down sofa -as if it was a coffin-; then, Peter raises the stack and puts it on his chest.  Enraged, Emilio complies and impales Peter with the stack, as Peter howls in pain and dies.

In the end, we hear a brief voiceover of Alva, lulling the baby she had from her rape incident, and saying: "Sleep, my son!!  Sleep, my sweet child!!".

Thanks Dr Mat!