Hacker News Reader

Comment by matdehaast | Hacker News Reader

What’s wrong with stored procedures?

They have to be managed like the database schema, which tends to involve a lot of ceremony. Coping with different function versions for different client versions can be inconvenient.

In cases where you want to run functions on the server for performance (rather than for hiding some underlying structure), it's often more convenient to maintain them together with other client code.

You can do things like creating functions in pg_temp once per connection, but more upstream support for this sort of setup would indeed be pleasant.

You can use prepared statements: https://www.postgresql.org/docs/current/sql-prepare.html

Sometimes you can, but sometimes you want something that can be called as a function.

And there's no PREPARE IF NOT EXISTS, so you either have to track yourself whether you've already done it or grub around in pg_prepared_statements.

not powerful enough, I want programming capabilities. composing statements and branching

DO?

https://www.postgresql.org/docs/10/sql-do.html

you can't pass input to those

I don't see why you counldn't use prepared statements.

I DON'T want that.

Maybe if there is a case where things are absolutely faster to do in the DB, but often times, you get better performance and scaling when you ship that stuff off to the application (it makes things easier to maintain as well).

I'm currently dealing with a legacy system where some wing-nuts decided to push all the business logic into the DB. It is really REALLY hard to improve things like performance and scaling when that happens.

Thanks a lot, didn't know about temp functions. You're hitting the nail on the head with where I want to go with this. I wish postgres put more attention into this kind of interfacing layer.

Reply on news.ycombinator.com