I don't have a PS3 or a Wii. But I do have DrNokSnes on my N900 and I saw videos on hooking up a Wii/PS3 controller to the N900 so you can play SNES games on the TV, etc. I also play DOOM, Quake 2/3, PSX, Duke Nukem 3D. What controller would be best for me? PS3 or Wii? Do I need to buy a certain model? What works best with the N900?

i'd go for the ps3 pad, more buttons = more compatibility with games, afaik any game that allows definable keys will work fine, i use mine for the emulators, altho when connected the phone does suffer a slight performance hit.
any wireless model will work, use "glove pie" on a pc with the pad connected via cable to reprogram the bluetooth address of the phone into it.
download the drivers etc on phone and off ya go!!!

though i'm not a fan of the dualshock at all, it seems to be the better choice for the n900.
if you're fine with only playing nes-games, a wiimote will do the job as well (obviously), but any game that requires more than a dpad and 2 button will be complicated with a wiimote alone.

while we are at it: does the classic controller work on the n900 by now?

Nope, the wii control app hasn't been updated in months, it is really rather useless-go with a PS3 controller.. just make sure its official or it won't work

What about the Xbox 360 controler, could the FM transmitter be modified to support the freequince range the controler opperates on?

If you wait a bit you can get the GameGripper, it's basically a pad that you put over your ordinary N900 keyboard and thus you can have the N900 in your hand while playing. It should be available end of June - Beginning of July. Look here(on the bottom there's a note about N900): http://www.game-gripper.eu/

EDIT: Oh, and the battery life of your N900 will be better compared to if you would use a Wiimote or a PS3 controller