9 ms·

What’s wrong with stored procedures?

mjw1007 7y ago
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.
- manigandham 7y ago
  You can use prepared statements: https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html
  - mjw1007 7y ago
    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.
  - janpot 7y ago
    not powerful enough, I want programming capabilities. composing statements and branching
    - megous 7y ago
      DO? https://www.postgresql.org/docs/10/sql-do.html https://www.postgresql.org/docs/10/sql-do.html
      - janpot 7y ago
        you can't pass input to those
        megous 7y ago
        I don't see why you counldn't use prepared statements.
    - cogman10 7y ago
      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.
- janpot 7y ago
  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.

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.

manigandham 7y ago
You can use prepared statements: https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html
- mjw1007 7y ago
  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.
- janpot 7y ago
  not powerful enough, I want programming capabilities. composing statements and branching
  - megous 7y ago
    DO? https://www.postgresql.org/docs/10/sql-do.html https://www.postgresql.org/docs/10/sql-do.html
    - janpot 7y ago
      you can't pass input to those
      - megous 7y ago
        I don't see why you counldn't use prepared statements.
  - cogman10 7y ago
    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.
janpot 7y ago
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.