Re: passing parameters to multiple statements

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(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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
>