The languages for writing it are not as comfy as traditional programming languages, which affects how expressive and maintainable your code will be. The tools for debugging a regular language might also be better than debugging application logic in SQL. Having done some of this in T-SQL, it's very tedious day-to-day compared to writing C#.
I did a fair bit of T-SQL back in the day. You're right: it's not fun.
It's okay for shuffling data around and doing SQL-y things in a slightly procedural way. Inserting rows, copying rows. For anything complex... well, it's not made for that.
But even SQL Server lets you call .NET CLR code now: https://learn.microsoft.com/en-us/sql/relational-databases/s...
And of course Postgres supports Python, etc etc etc.
Don't expect your experience with any other DBMS to give you an idea about how nice it is to program in Postgres.
It's still not as nice as creating some independent code. But Postgres is quite nice to program in.
Do you have any interesting blog links?
But also, you don't need extensions to enable other languages, postgres allows those out of the box (through the C-ABI). Anyway, that is not very compelling, because plpgsql is quite good already.