In the final episode the purpose of the Night's Watch is further explained by Tyrion and it fits with Aegon's answer.
Tyrion: Giving you to the Unsullied would start a war. Letting you walk free would start a war. So our new king has chosen to send you to the Night's Watch.
Jon: There's still a Night's Watch?
Tyrion: The world will always need a home for bastards and broken men.
Game of Thrones, Season 8 Episode 6, "The Iron Throne"
It depends on how you interpret the oath and the function of the Night's Watch. The oath is as follows, emphasis mine:
"Hear my words, and bear witness to my vow," they recited, their voices filling the twilit grove. "Night gathers, and now my watch begins. It shall not end until my death. I shall take no wife, hold no lands, father no children. I shall wear no crowns and win no glory. I shall live and die at my post. I am the sword in the darkness. I am the watcher on the walls. I am the fire that burns against the cold, the light that brings the dawn, the horn that wakes the sleepers, the shield that guards the realms of men. I pledge my life and honor to the Night's Watch, for this night and all the nights to come."
A Game of Thrones, Jon VI
Their are effectively two parts to the emphasised bits.
I am the sword in the darkness. I am the watcher on the walls. [...] the shield that guards the realms of men
This part is generic and so one could argue that they are still needed just as a function to protect those in Westeros from outside threats. In fact in recent years the White Walkers and the threat they pose has long been forgotten and the function of the Night's Watch has mainly fallen down to keeping the Wildlings out.
"You and everyone else," said Matthar. Every man who wore the black walked the Wall, and every man was expected to take up steel in its defense, but the rangers were the true fighting heart of the Night's Watch. It was they who dared ride beyond the Wall, sweeping through the haunted forest and the icy mountain heights west of the Shadow Tower, fighting Wildlings and giants and monstrous snow bears.
A Game of Thrones, Jon V
I am the fire that burns against the cold, the light that brings the dawn
This part falls under the very specific job of the Night's Watch that is guarding Westeros against the White Walkers and the Army of the Dead. In fact that is the main reason why they were formed in the first place and some still dream of it as true.
"Denys Mallister writes that the mountain people are moving south, slipping past the Shadow Tower in numbers greater than ever before. They are running, my lord … but running from what?" Lord Mormont moved to the window and stared out into the night. "These are old bones, Lannister, but they have never felt a chill like this. Tell the king what I say, I pray you. Winter is coming, and when the Long Night falls, only the Night's Watch will stand between the realm and the darkness that sweeps from the north. The gods help us all if we are not ready."
A Game of Thrones, Tyrion III
As Aegon says the Others/White Walkers were defeated once before when they were driven back and the Wall was raised so there's nothing stating that they might not come back.