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 09:33:05AM -0700, Konstantin Izmailov wrote:That's called shooting yourself in the head.
> Some companies have policy to stay DB agnostic, i.e. use standard
> SQL only.
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@xxxxxxxxxx> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@xxxxxxxxx
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
