I haven't brewed with pumpkin, but I have made alot of food with pumpkin & squash. If you want to use fresh produce, I wouldn't recommend anything that is aimed at being carved for Halloween. They are typically flavorless. Many stores will sell "pie pumpkins", which can be much more flavorful.
If I were going to brew a "pumpkin" beer (and I don't care too much for pumpkin spice in beer either), I would probably use acorn or butternut squash instead, as both tend to be more flavorful. Also, I find that smaller squash have more flavor than the larger ones.
If you have access to a farmer's market, talk to them about what you want to do, and they should be able to steer you to the right fruit.