Skip site navigation (1) Skip section navigation (2)

Re: "stored procedures" - use cases?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: "stored procedures" - use cases?
Date: 2011-04-27 22:28:48
Message-ID: 4DB898A0.9010901@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Greg,

> 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. 

I don't see how this is different from wanting to capture error output,
which would face the same issues.  You seem to be wanting to make a hard
feature easier by telling me that I don't actually want the things I
want.  Wanna make it even easier?  Then Stored Procedures are just
functions without a return value.  That's a 40-line patch.  Done!

> 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.

You build your applications your way, and I'll build mine my way.  I'll
just ask you not to try to dictate to me how I should build
applications.  Especially, since, based on the responses on this thread,
a LOT of people would like to have multitransaction control inside a
stored procedure script.  I suspect that your experience of application
development has been rather narrow.

> 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)') ?

Actually, you can already sort of do that using XSLT.   So I don't
necessary think that's a prohibitive idea, depending on implementation.
 After all, many of the new non-relational databases implement exactly this.

>>> 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.

You could do it by using application code.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

In response to

Responses

pgsql-hackers by date

Next:From: David E. WheelerDate: 2011-04-27 23:24:56
Subject: Re: "stored procedures" - use cases?
Previous:From: nadosilokDate: 2011-04-27 22:26:22
Subject: Re: [ANNOUNCE] PostgreSQL Core Team

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group