Exploring PL/pgSQL: Strings, arrays, recursion, and parsing JSON

Well, PL/pgSQL is compiled but there is no semantics checking at compile time. So while you may have better performance depending on use case etc. it can be a bit hard to work with.

I wrote a lot of Oracle PL/SQL at my last job* and I will say if you're not familiar with that kind of database language I would highly recommend Python over it. First think about whether you really want to have business logic in your database and then choose what you know best.

*: It was messy and I am glad that that job is over.

PL/pgSQL is a first class language with first class semantics. As @sureglymop mentioned, you're probably going to get better performance than an interrupted or compiled language.

Another consideration is portability. Perhaps you're migrating to or from another database that has its own Procedural Language. You're probably going to have an easier time dealing with PL -> PL when migrating stored procedures.

There are times when it's just easier and more intuitive to glue a bunch of SQL together using a PL, than whipping up some python/js/etc...

Also keep in mind that depending on the DB, you may get a scripting language like python or js, but you can't use any third party libraries.

To my knowledge, AWS RDS Postgres does not support PL/Python, if that's your database.

I find PL/pgSQL preferable if only because it fits most naturally in the database environment. Queries fit very naturally into the code with little special treatment; being able to rely on the full power of PostgreSQL's type system in my logic without any additional accommodations; exception/error handling feeling more natural and integrated;and as others have noted, certain performance advantages (usually).

PL/pgSQL has a fairly low complexity in my experience compared to many other languages... More often it's the SQL/database conceptual part that I see people struggling with most. PL/pgSQL in some ways makes that a little more front and center... But if you're writing functions and procedures you're really not going to escape having to come to terms with the database. May not be the issue you were experiencing, but expect that to be more common.

I've stuck with thinking of Pl/* as an aid to data normalization. This way i can look up records like country codes in the database but keep business rules in the app.

I use plpgsql for constraints but try to avoid any form of mutation mostly based on other folks horror stories.

There's a parameter that sets a memory consumption limit on potentially recursive functions: max_stack_depth.

(https://www.postgresql.org/docs/14/runtime-config-resource.h...)

I personally use Sqitch to manage database migrations. Makes it very easy to set up test databases for all development work.

I use this https://github.com/golang-migrate/migrate in a deploy step to each environment with the upgrade / downgrade scripts committed to the repo alongside the code. The scripts can do pretty much anything you need to do in PG including defining and executing functions.

I don't say this from experience but DDLs in postgres are transactional so I don't understand the problem with wrapping create or replaces in a transaction with no need to cross your fingers.

The sort of algorithm you have in mind is usually called a soundex (“sound-index”). Fairly confident order of performance would be PL/pgSQL > Lua > Python.