Ok, I accept reasoning that DB agnostic development is propbably a bad idea.
The question should probably be re-introduced as "Stored Procedures against multiple statements in Postgres".
Here is my client opinion:
"
SP’s have their place, as with any development, there’s many reasons for and against any method and there are many methods. I’ve always taken the view – use the right tool for the job.If you always use stored procedures, you end up with tons and organising them is a nightmare, they are good if you need to reuse the SQL <<<cut>>>
Yes especially flexibility – we don’t want to have a sp for every insert on every table in our db that inserts then returns the currValue and I would like to create a temp db then query that as a second query instead of using retain connection which will give me better performance – only one db connection attempt as opposed to two and I don’t want to create 50 sp’s to do it on each table. I think their good enough reasons, business or otherwise.
I notice this attitude a lot in postgres community – it’s like the reasoning for not allowing cross db queries – “blah blah should have designed db better blah blah”, what they don’t realise is, that some people might want to have an archive db or warehouse and to get data into it would be a lot easier with cross db queries. <<<cut>>>"
Anyway, here is what I understood:
1. If client app needs support for multiple statements with parameters in PostgreSQL, I have to provide a software layer above libpq that includes parser, metadata cache, etc.
2. "BEGIN; INSERT ...; SELECT lastval(); COMMIT;" would work but is not portable because of lastval().
3. No change is needed in libpq since parser can split the multiple statements in the layer above.
Thank you for the valuable discussion!
Konstantin
On Tue, Nov 17, 2009 at 9:16 PM, David Fetter
<david@fetter.org> wrote:
On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov wrote:
> Some companies have policy to stay DB agnostic, i.e. use standard
> SQL only.
That's called shooting yourself in the head.
Unless you have a very, very specific, business-critical reason to pay
this huge cost, you should never attempt it. That some companies have
silly, self-destructive policies is not a reason for anybody not
working there to pay attention to same.
More details on why it's so inevitably expensive below:
http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html
http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
