That was possible before when targeting RV32. If I understand correctly, this makes it possible for LLVM to use all RISC-V extensions supported by the core with a simple command line (the patch describes what features the core supports), resulting in better-optimized code.

However, to run any language on the Pi Pico, that language still needs to interop with Pico's SDK, which you'd have to do manually if no one created bindings yet.

That was already true given that the main RP2350 cores are just normal Cortex-M33s. The Hazard3 cores are just selectable replacements, and this is mostly just adding support for the specific set of RISC-V oddities taped out into Hazard3 on the RP2350 (this class of issue is going to be a big issue for the RISC-V ecosystem moving forward, imo).

But yes, normally when a new LLVM backend lands most frontends will immediately gain support; this is the goal of the LLVM architecture. Peripheral drivers and a standard library that functions on a given hardware target are an exercise for the reader, although most LLVM frontend languages can bridge to C in some way or another.