As far as playable characters are concerned, Pumpkin is a strange one. Unlocked after clearing out the game as Joachim Armster, Pumpkin has little reason to actually exist in the game. Created as a joke by series-head IGA, the character was thrown in simply as a lark, something funny to laugh at while you went through the game one more time. He has no story, nothing really special about him. He's just a weird little kid-like doll with a pumpkin for a head that kills vampires.
Although funny for the creators (and certainly amusing as a one-off lark for players), Pumpkin as a charater would have made more sense if he were anyone else, or if pumpkin children has shown up ealier in the series. There are other enemies they could have used instead that might have sold the joke to unknowing audiences more (which was eventually the case with the inclusion of the Old Axe Armor as a playable character in Portrait of Ruin). As it is, he's a joke without much of a punchline, really.
Give the creators credit, though, as they did know how to try and sell a joke. After Lament of Innocence, ol' Pumpkin was included in the follow up, Castlevania: Curse of Darkness as one of that game's "innocent devils", the powerups that the game's hero, Hector, uses to explore the castle. So at least the creators had fun with this weird little dude.
Playing as Pumpkin:
More than even Joachim in Lament of Innocence, Pumpkin is very much the embodiment of a glass cannon. Extremely weak, Pumpkin can't take much damage without dying. He can unleash devastating attacks with his Vampire Killer and pumpkin bombs. Why does he have the Vampire Killer? Who knows. THere's no story included to explain it. His bouncy pumpkin bombs are pretty fun to throw, though, and they can be upgraded with the various orbs hiddens throughout the castle.
Pumpkin's inclusion in Curse of Darkness is much more limited though. As innocent devils, Pumpkins can be summoned to deal very slight amounts of damage. They're incredibly weak and are considered some of the most useless devils in the game. The only reason to use them is that they give huge stats bonuses to Hector, but considering how limited they are in all other respects, it's doubtful most players gave them any time beyond a simple laugh (which, admittedly, was probably the point to begin with).