Re: passing parameters to multiple statements

2009/11/18 Konstantin Izmailov <pgfizm(at)gmail(dot)com>:
> 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

there are lot of myth about stored procedures.

a) wrap every SQL statement to procedure is technique used on T-SQL and Sybase.
b) on Oracle and DB2 procedures are used together with views
c) if you use trigger, you don't need some strange multistatments.

d) what is more readable code (on client)?

d1:

BEGIN;
insert into table students values($1,$2,$3);
insert into audit values('students', lastval(), .....)
COMMIT;

or
d2:

SELECT register_new_student($1,$2,$3);

thats all;

try to look on http://www.postgres.cz/index.php/PL/pgSQL_%28en%29
mainly on http://www.postgres.cz/index.php/PL/pgSQL_%28en%29#Recommendation_for_design_of_saved_procedures_in_PL.2FpqSQL_language

Best regards
Pavel Stehule

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