In a real space navy, assuming of course that there could ever be such a thing, after Riker rescues Picard from the Borg, Picard would never be authorized to command a space broom, much less a starship, ever again (except in limited contexts, such as one described below).
The only plausible way to keep Picard on the show is as an adviser or other kind of passenger, possibly even as Beverly's husband. Riker retains command, maybe Shelby is the new number one.
The main characters in the crew would accept that he's basically recovered, or in recovery, but Picard confronts new challenges. Some of the crew might not trust him, the admiralty in aggregate doesn't trust him, though he may have some allies there. Starfleet Medical still isn't certain all Borg technology has been removed. Dr. Crusher and Troi may both have orders from higher-up to keep an eye on him. He may need ongoing counselling to help him with his PTSD and feelings of guilt. Borg technology might actually be discovered still in him from time to time that needs to be neutralized.
There's still lots that can be done in a general episodic space adventure under that format, lots where Picard's experience, knowledge, and judgment are essential to away missions in known space or encounters with unknown aliens on the frontier. For example, "Chain of Command" could take on a new dimension where Picard's mission is explicitly viewed by Starfleet Command as a suicide mission, an angle that is completely absent from the scripted episode.
There's also room for encounters with the Borg. For example, Hugh would still have a place in this new format, and the arguent of whether to deploy a virus to destroy the Borg still has a similar place.
Anyway, that's my take.
Someone else awhile back suggested that he become a diplomat, Ambassador Picard, but stay on the ship, and I absolutely love the idea. The idea of him becoming a little bit dependent, like you said, maybe marrying Beverly, becoming one of those old scientists/diplomats with his wife looking after his well being as a care taker, and having to deal with all of the changes that brings, could have worked, and had a few levels of irony as well.