no worries about the 'vanilla Card' - I googled it too, and very probably felt exactly the same when I found out 🙂
I did not actually try every 3rd party card in my mac pro myself, but since the drivers are present (included from OSX 10.7.5 onwards) and the hardware is compatible - it should work.
There will be on drawback on non mac-certified cards:
You will have a black screen until the login window appears. In addition to looking spooky this circumstance can be a bit of a hassle when it comes to switching between different operating systems [still possible, but you have to know the according keystrokes by heart]
Also you will have to draw the ventilation power from one of the optical drivebays, since most of PC cards have different power connectors onboard - for stability reasons I would leave this optical bay empty.
For the first few hours I would keep an eye on the temperatures of the graphic card, if it gets too hot: a stronger fan will set you back about 15 $, that pays off.
There are some reports from People out there stating it works, with different (non-mac) nvidia cards.
Before buying a specific model I would look for some reviews (obvious, but you never know..).
One important thing ro consider is you mac pro model: The Bottleneck problem.
There is no use in putting an expensive high performance card into your mac if your bus can't deliver the data
in time.
Assuming your mac is a bit older, you might find some useful information on different working configurations and benchmarks. This site is from 9/2012 but has some additional benchmark information and could come in handy if one has to choose an appropriate card:
http://www.barefeats.com/rogue02.html (Thanks, Rob Art Martin!)
Hope this helps