It's obvious there's nothing physiological going on because you're certainly having erections with self-pleasuring, etc. So the problem has to do with being sexual with a partner and what that may represent to you.
Welcome to your 40’s. When you're younger and just beginning to be sexual with others, erections pop up everywhere--including when you don't want them! Post-pubescent men are highly excitable. Most men in their 40's find that they need more direct and intense stimulation. As you age, you'll find that erections sometimes take longer, and even come and go. Again, this is not an indication if ill health, but just part of life. Sexual interest ebbs and flows as well, depending on other circumstances in your life.
Viagra doesn’t create sexual interest or enhance sensation; it merely increases blood flow to your penis. If you have an organic condition which affects blood flow, then Viagra can, indeed alleviate this concern. However, if you’re conflicted about sex, or uninterested, or some other psychological or emotional concern is preventing you from being turned on, all the pills in the world can’t help that. And the more stress you’re under, the less energy your body has to respond sexually. Two other reasons why you might not be having erections are that you’re bored with your current partner or the sexual activities you’re doing together, or that there are some conflicts in the relationship and you’re feeling anxious or angry. Something is getting in the way of your enjoyment, and it’s your job to find out what it is.
Our society does a terrible disservice to men by raising them to think that their penis has to be 10 inches long, hard as a rock and last all night. It’s no wonder that many men feel insecure about their sexuality. Great sex involves your whole body; in fact, having great, mutually satisfying sex is usually quite the opposite of what you see in those performance-oriented sex videos.
The more you think of sex as being about performance, the more worries you'll have. If you're stressed, you'll probably worry even more. And this can be a vicious circle. For most of us, sex is about fun and pleasure. If you think of it as performance, or a “job,” you’ll set yourself for a stressful experience. Remember: Performance can be the enemy of pleasure and fun.
Men receive so many messages that sex is about “performance” and pleasing a partner, rather than just enjoying the pleasure of it. Everything is goal-oriented—like a football game. The other message that many men receive is that sex is somehow dirty and wrong, unless you’re doing it for reproductive purposes. Sometimes this can creep into our unconscious thoughts and sabotage any pleasure.
And please don't believe all the myths you've heard--especially about women. Women are as diverse as men. However, most women are much more interested in a caring, considerate partner than merely a hard penis.
It seems to me there’s something psychological contributing to your concern. A good first step would be to explore what’s going on. Start by reading a book about male sexuality to discover what other men are sharing about their experiences. I recommend “the New Male Sexuality” by Bernie Zilbergeld, Ph.D., widely available online, both used and in paperback. Best of luck to you. Dr. J