4 ms·

I've written a bit of PL/pgSQL, but I'll be honest, the basis of most of it has been copy/paste from Stackoverflow and tweak a couple of things here and there.

I've written a bit of PL/pgSQL, but I'll be honest, the basis of most of it has been copy/paste from Stackoverflow and tweak a couple of things here and there. More recently I learned that you can also write python functions 'natively' (ie no need to install any modules) in postgres, which honestly seems like a much better idea all round than learning PL/pgSQL, even if you don't know any python already. Can anyone comment on if there are any feature differences between the two, or why that might be wrong?

sureglymop 5y ago
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.
chrisjc 5y ago
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.
code_biologist 5y ago
To my knowledge, AWS RDS Postgres does not support PL/Python, if that's your database.
- davnicwil 5y ago
  Ah that's interesting to know, thanks. Part of the premise here was it being available everywhere including the managed services. If that's not true, or it's not trivially installable, it removes much of the advantage I think.
sbuttgereit 5y ago
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.

sureglymop 5y ago

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.

chrisjc 5y ago

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.

code_biologist 5y ago

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

davnicwil 5y ago
Ah that's interesting to know, thanks. Part of the premise here was it being available everywhere including the managed services. If that's not true, or it's not trivially installable, it removes much of the advantage I think.

sbuttgereit 5y ago

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.