News for hackers. Stuff that matters.
Re:Ask HN: Have you ever used PL/pgSQL – the PostgreSQL prog... by tomlock on Sunday September 02, 2018 @08:40PM (#17899476)
When I didn't know any better I wrote a finite state machine in it.

In my opinion, try and use as much SQL as you can first. Functions are often an optimization barrier for the postgresql query planner. The postgresql implementation of SQL has a bunch of neat functionality in it that replaces things that often happen in apps, like window functions, and the docs are so great.

They are hard to debug. Try to keep their functionality small.

My experience, overall, is positive. Consider using it if the amount of data transferred to the app and evaluated there is getting absurd. But try to creatively solve the problem with pure SQL first before moving to functions.

Re:Ask HN: Have you ever used PL/pgSQL – the PostgreSQL prog... by arisb on Monday September 03, 2018 @09:32AM (#17902475)
Functions don't have to be optimization barries. If you mark them as stable or immutable I believe postgres will inline them whenever they are used such that they can be optimized.
Re:Ask HN: Have you ever used PL/pgSQL – the PostgreSQL prog... by davidgould on Monday September 03, 2018 @05:04PM (#17904857)
Optimization barrier means something a bit different, it means that the optimizer can't push down filters or change the join order, or flatten subqueries into joins. That is, it splits the query into parts that are separately considered for rewriting and plan selection.

In postgres, CTEs are optimization barriers, that is, the implementation plans and executes the CTE separately and the main query consumes the result of the CTE. Pure SQL functions can sometimes be inlined, that is, made part of the containing query, but functions in the other languages cannot. In particular, if a function runs queries itself those happen in a new execution context (portal) and don't share anything with the function callers plan.

Re:Ask HN: Have you ever used PL/pgSQL – the PostgreSQL prog... by tomlock on Monday September 03, 2018 @08:38PM (#17905767)
These are both great observations.

VOLATILE/STABLE/IMMUTABLE is definitely a choice worth considering and making in an educated way when writing a function.

Also, using WITH to create CTEs makes SQL easy to read, but sometimes will slow down a query considerably. I have a feeling that new releases will make attempts to mitigate this issue in some cases.