Yeah, all of my reasoning was based on a presumption that the stake actually stuck in the beast it hit; if the stakes were to pierce all the way through and leave the target with nothing but a hole, it would be next to useless, little more than a large spear you could use at a distance, but only once. As I pointed out the merit of the weapon would be disable, cripple or immobilize beasts.

And I agree, if anyone would be willing to see this weapon made, it would probably be the Powder Kegs - they were the ones behind the mounted machine gun, after all - though if it had been them, I'd almost have expected them to tie an explosive to the stake or something. That said, while there's a lot we don't know about the Powder Kegs, we do know that most of them supposedly died or turned into beasts trying to purge Old Yharnam before they set the entire thing on fire, and that the remainder joined Djura in trying to protect the beasts instead of killing them, making themselves an enemy of the Healing Church. Considering that the player-Hunter (going by "highest rate of completion") killed Djura and a few other Hunters in Old Yharnam, and the only other place I recall seeing Powder Kegs weapons being the Hunter's Nightmare - where all Hunters are dead - I'd say that if the Powder Kegs still exist, I doubt they have the resources or opportunity to be making much of anything.
The only way I could really imagine the weapon existing is for the Powder Kegs to have built it (or mostly built it) before the Night of the Blood Moon, and that someone else found and/or completed it after, perhaps during the effort to rebuilt Yharnam.

EDIT: As for church giants being able to wield the weapon... eh. They don't strike me as particularly accurate or dexterous fellows. I have my doubts they would be able to wield it very effectively, even if they could reasonably haul it around and shoot it.