Obviously there is nothing wrong with teachers drinking, though it may be seen as unprofessional for a teacher to be intoxicated while in front of their students, or in a public setting. For example we know Hagrid is prone to having a few too many drink here and there, but typically away from students.
Have we ever seen the teachers at Hogwarts drunk in front of students?
Sybil Trelawney was perpertually drunk. In public. In front of students. In school.
'How – dare – you – aaaaargh!'
The noise was coming from a corridor nearby; Harry sprinted towards it, his wand at the ready, hurtled round another corner and saw Professor Trelawney sprawled upon the floor, her head covered in one of her many shawls, several sherry bottles lying beside her, one broken. (HBP)
Several times he passed her in the corridors in itself a very unusual occurrence as she generally remained in her tower room muttering wildly to herself, wringing her hands and shooting terrified glances over her shoulder, and all the while giving off a powerful smell of cooking sherry. (OotP)
Professor Trelawney was standing in the middle of the Entrance Hall with her wand in one hand and an empty sherry bottle in the other, looking utterly mad. Her hair was sticking up on end, her glasses were lopsided so that one eye was magnified more than the other; her innumerable shawls and scarves were trailing haphazardly from her shoulders, giving the impression that she was falling apart at the seams. (OotP)
'Oh, of course,' said Professor Trelawney with an angry, drunken titter. ...
Professor Trelawney seemed too tipsy to have recognized Harry. (HBP)
In OotP film, she takes a swig from her flask straight in the middle of Welcome Feast too.
Slughorn not only drunk in front of students, but gave them alcohol
'Don't mention it, m'boy, don't mention it,' said Slughorn, as Ron collapsed into a nearby armchair, looking devastated. 'Pick-me-up, that's what he needs,' Slughorn continued, now-bustling over to a table loaded with drinks. 'I've got Butterbeer, I've got wine, I've got one last bottle of this oak-matured mead... hmm... meant to give that to Dumbledore for Christmas... ah well...' he shrugged '... he can't miss what he's never had! Why don't we open it now and celebrate Mr Weasley's birthday? Nothing like a fine spirit to chase away the pangs of disappointed love...'
... 'There you are, then,' said Slughorn, handing Harry and Ron a glass of mead each, before raising his own. 'Well, a very happy birthday, Ralph –' (HBP)
Dumbledore, Slughorn's old friend, gave Harry a drink in front of entire Dursley family. And to Dudley too, presumably.
A third twitch of the wand, and a dusty bottle and five glasses appeared in midair. The bottle tipped and poured a generous measure of honey-coloured liquid into each of the glasses, which then floated to each person in the room.
'Madam Rosmerta's finest oak-matured mead,' said Dumbledore, raising his glass to Harry, who caught hold of his own and sipped. He had never tasted anything like it before, but enjoyed it immensely. The Dursleys, after quick, scared looks at one another, tried to ignore their glasses completely, a difficult feat, as they were nudging them gently on the sides of their heads. Harry could not suppress a suspicion that Dumbledore was rather enjoying himself. (HBP)
For that matter, Slughorn and Hagrid get roaring drunk in front of Harry in HBP, after Aragog's funeral. But I suppose that doesn't qualify as "publicly".
'Lets get inside and have a drink. Get on his other side, Harry... that's it... up you come, Hagrid... well done...'
They deposited Hagrid in a chair at the table. Fang, who had been skulking in his basket during the burial, now came padding softly across to them and put his heavy head into Harry's lap as usual. Slughorn uncorked one of the bottles of wine he had brought. (HBP)
In their defense, I suppose, Harry was refilling their bottles, to get them drunk.
While not teachers, the painting figures drink too, in public:
'Precisely,' said the Fat Lady in a feeble voice, and swung forward to reveal the portrait hole.
'What's up with her?' asked Harry.
'Overindulged over Christmas, apparently,' said Hermione, rolling her eyes as she led the way into the packed common room. 'She and her friend Violet drank their way through all the wine in that picture of drunk monks down by the Charms corridor. Anyway...' (HBP)
While not a Hogwarts teacher, the orphanage head for Tom Riddle seemed to be quite a drinker too:
Mrs. Cole's eyes slid out of focus and back again as she gazed intently at the blank paper for a moment.
'That seems perfectly in order,' she said placidly, handing it back. Then her eyes fell upon a bottle of gin and two glasses that had certainly not been present a few seconds before.
'Er – may I offer you a glass of gin?' she said in an extra-refined voice.
'Thank you very much,' said Dumbledore, beaming.
It soon became clear that Mrs. Cole was no novice when it came to gin drinking. Pouring both of them a generous measure, she drained her own glass in one gulp. Smacking her lips frankly, she smiled at Dumbledore for the first time, and he didn't hesitate to press his advantage. (HBP)
Wait what? Seems Dumbledore conjured that gin as well? Well...
We sure have.
During a few of the feasts it's shown that the teachers can have alcohol. Here's a snippet from Harry's first Christmas dinner at Hogwarts.
Harry watched Hagrid getting redder and redder in the face as he called for more wine, finally kissing Professor McGonagall on the cheek, who, to Harry's amazement, giggled and blushed, her top hat lopsided.
So we know Hagrid has, and considering McGonagalls reaction (and lopsided hat!) I'd say she was a little tipsy herself.
Yes, Hogwarts teachers would occasionally get drunk in public settings, where students could see them. Usually Hagrid.
Harry watched Hagrid getting redder and redder in the face as he called for more wine, finally kissing Professor McGonagall on the cheek, who, to Harry’s amazement, giggled and blushed, her top hat lopsided
Dumbledore led them in a few of his favorite carols, Hagrid booming more and more loudly with every goblet of eggnog he consumed.
‘Four pints of mulled mead –’
‘Ta, Rosmerta,’ said Hagrid.
(It's unclear how drunk that would get Hagrid, given his size and heritage. But he gets very emotional during their conversation about Harry's parents. And Slughorn actually manages to outdrink Hagrid in HBP.)
To many instances to cite here, probably.
Since you asked about public intoxication, I've left off the many more instances of Hogwarts professors drinking privately with their students (Slughorn and Dumbledore both drink with Harry in HBP). In the UK, generally speaking, it's perfect legal for older teenagers to drink under adult supervision.
