Re: "stored procedures" - use cases?

On Wed, Apr 27, 2011 at 6:48 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> If you pursue your argument a little further, Greg, why do we have
> functions at all?  We could do it all in the application.
>
>> Autonomous transactions have value on their own. But it's not so that
>> you can run create index ocncurrently or vacuum or whatever.
>
> Why not?  Why are you so intent on making my life harder?

Because we want to be able to manipulate data in queries in
data-type-specific ways. For example we want to do aggregations on the
result of a function or index scans across a user data type, etc. If
all the functions do is implement application logic then you end up
having half your application logic in the application and half in the
database and it's hard to keep them in sync.

To take the argument in the opposite extreme would you suggest we
should have html formatting functions in the database so that people
can have their entire web server just be print $dbh->('select
web_page(url)') ?

>> They're
>> useful so that a single session can do things like log errors even
>> when a transaction rolls back.
>
> That's *also* an excellent use case.

What makes it an excellent use case is that it's basically impossible
to do without autonomous transactions. You can hack it with dblink but
it's much less clean and much higher overhead.

--
greg