There are two melodies to which these lyrics can be sung. The first merely consists of a single note strummed on a guitar, and was composed by Neil Hannon of The Divine Comedy, whom we all know to be capable of much greater things. This is the tune composed by Ted and Dougal, and which prompts Father Jack to destroy Ted's guitar with a shotgun.
The second is altogether more elaborate, and was again composed by Hannon, who is also responsible for the theme tune to Father Ted. This is the tune which Ted finds in an instrumental on the B-side of Dougal's favourite record. Learning that the Scandanavian band who recorded the tune are all long dead, Ted decides to set "My Lovely Horse" to it, and a classic is born. Unfortunately, it is later revealed that the track is not as obscure as once thought, and Ted and Dougal are forced to revert to their original composition. This results in their being selected to represent Ireland in the Eurovision Song Contest, their song being the least likely to win and thus force RTÉ to stage the event for the umpteenth time.
Since the airing of this episode, Neil Hannon's version of the song has become a sing-song favourite, and it's not unusual to find bands including it in their live sets. Ask anybody who has seen this episode and they will be able to give you a passing rendition of the tune.
More info on the episode is at A Song For Europe. Thanks to bol for pointing out that both tunes were the work of Neil Hannon.