Although I know many individual cops who can't wait for legalisation. They're tired of wasting time on weed when heroin and opioids are destroying whole communities.
I would imagine that the reason why PDs tend to be against legalization, is because it provides them with a much broader list of targets for civil asset forfeiture, which goes into their budgets.