Well, I've sucked a lot of cock in my life (maybe 15 different guys) and I have to say that, really, I don't find the actual sucking to be a turn on and it's not something I really enjoy.
What I do enjoy and am turned on by is the thought of submitting to another male (particularly one who has or will take my wife). We have been taught, as guys, that one of the most humiliating things we can be ****** to do is to suck another man. Thus, that feeling of submission and humiliation is just a complete turn on to me.
I will tell you that most of the times that I suck cock, my dick is not hard at all. As soon as the guy starts saying things like "you like that, don't you" or "you're a good little cock sucker," then I get hard. Also, if I think about what I'm doing (I'm sucking this bull's cock, he's dominating me and about to dominate my wife) then, again, I get hard.
Once, a few years back, I was in between women for about a month. I really had just finished back to back bad relationships with woman and thought, "hell, I'm bi, I've sucked dick, maybe I just need to be with a guy." So. I'm at this bar one night and a 22 year old blond, college student is sitting next to me at the bar drinking. He's OK looking and pretty smart and we ended up drinking and talking all night. The bar closed and we wanted more booze so headed back to my place to drink. I got the feeling through the night that he might be bi and when we got to my place, I started to drop very slight hints. He, it turned out, was bi, however, was definitely a "top." never had had anal sex with a man but liked guys sucking him off. He was a little cocky and I did give him a long and hard blow job. A few times, throughout, he said things like "I bet you like that big cock in your mouth, don't you" and that's when it dawned on me, I didn't, really, and I wasn't hard, at all.
So, I guess the answer is psychologically it can be a turn on. Under the right circumstances, it can be a turn on and something I enjoy. But, overall, I'd have to say no.
As a brief follow-up, the next night, I'm in the same bar and pick up a 21 year old blonde college girl. I was hard in the car (before we even get to my place) and that was with just a little kissing.