A rod is somewhat like a wand. Basically they have special abilities that are limited either by type of action taken (Move, standard, full-round, etc.) or number of daily charges rather than a finite number of charges that can be used in any quantity per day like a wand.
In addition, they generally also act as light maces or clubs (1d6 damage, x2 crit).
I have no clue if they can be Masterwork and therefore enchanted by RAW, but I would assume not.
only certain rods function as maces! The rest would most likely function as improvised weapons, giving you -4 to attacks, 1d6 damage and a 20x2 critical.
The primary purpose of rods is to give casters a boost. If melee is what you want to do, there are better weapons for you than a rod. Most players use rods because of their built-in metamagic feats.
however, some rods are more of a weapon-like quality. Rod of the Python (13,000 gp) is the cheapest "weapon-like" rod that I could find. It counts as a +1/+1 quarterstaff and could presumably be further enchanted. Unfortunately, this rod still leaves much to be desired as a weapon, and so does its brother the Rod of the Viper.
Moving on down the line, the Fiery Nimbus Rod seems to be pretty good for a weapon It's a bit more expensive at 22,300 gp, but it strikes as a +1 flaming burst light mace, which means it's only 4,000 gp more expensive than your standard flaming burst light mace, and it has an additional effect of the fiery nimbus, which is pretty impressive! If I were going to be a rod-wielder, I would probably pick this one!
Generally speaking, rods are too cost-prohibitive to serve well as melee weapons, but the Fiery Nimbus Rod might be an exception at mid-level.
Convince a rapier/scimitar wielding ally to pick up the feat Butterfly Sting, and your Fiery Nimbus Rod could really shine!
Well the problem with that idea really is that a light mace is a rather bad weapon choice on so many levels for a martial character. It's an ok backup-weapon for a caster, but that's really it.
...and Flaming Burst is overpriced. Compared to Flaming, it probably adds about quarter of a point of damage on average to your attacks, and costs the same as a +1 to hit and damage.
A rod of nettles can be a nasty weapon for only 18,000 gp.
An evil character could find a rod of the viper an attractive option at 19,000 gp.
A rod of withering has potential, but the save to negate makes it kind of weak at 25,000 gp.
A rod of thunder and lightning is probably one of the better values at 33,000 gp.