Re: [GENERAL] Functions

Dear PG members,I want to know that does begin and end in plpgsql are
related to begin transactions and commit? if yes/no how? If I start to
execute a function of multiple statements does PG commits each transaction
within a function, or whole function as a transaction.
Also if first statement makes inserts/updates a row, does it automatically
available to that particular function execution only or to any other
instances of same function?
Please give the details.

In my system, function are extensively used and cab called by different
clients as ODBC, JDBC and others.

Thanks for help,

CPK

On Mon, Sep 15, 2008 at 10:55 AM, c k <shreeseva(dot)learning(at)gmail(dot)com> wrote:
> Dear PG members,
> I want to know that does begin and end in plpgsql are related to begin
> transactions and commit? if yes/no how?

No. a plpgsql function is a transaction in its entirety. If called
within a transaction it inherits the parent transaction.

>If I start to execute a function of
> multiple statements does PG commits each transaction within a function, or
> whole function as a transaction.

Whole thing, all or nothing.

> Also if first statement makes inserts/updates a row, does it automatically
> available to that particular function execution only or to any other
> instances of same function?

To that function only. Until commit no one else should see the change.

> Please give the details.
> In my system, function are extensively used and cab called by different
> clients as ODBC, JDBC and others.

How called isn't all that critical, unless you've set some kind of
autocommit flag or something.

On Mon, Sep 15, 2008 at 11:22 PM, c k <shreeseva(dot)learning(at)gmail(dot)com> wrote:

> If I have a function having:begin
> insert into something ...
> select max(primary key) from something..
> end;
>
> does the second statement within a function can view the results after
> execution of first statement to get max(P.K.)?
>
> CPK
>
> On Mon, Sep 15, 2008 at 10:38 PM, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
>> c k wrote:
>> > Dear PG members,I want to know that does begin and end in plpgsql are
>> > related to begin transactions and commit? if yes/no how?
>>
>> No. All functions operate within a transaction.
>>
>> > If I start to
>> > execute a function of multiple statements does PG commits each
>> transaction
>> > within a function, or whole function as a transaction.
>>
>> You can have many statements (function calls) within a single
>> transaction. If you don't explicitly issue BEGIN ... COMMIT then each
>> statement is wrapped in its own transaction. Note that some client
>> libraries have "autocommit" options that do things their own way.
>>
>> > Also if first statement makes inserts/updates a row, does it
>> automatically
>> > available to that particular function execution only or to any other
>> > instances of same function?
>> > Please give the details.
>>
>> If you insert/update/delete one or more rows, they are visible outside
>> the function - it's all the same tables.
>>
>> > In my system, function are extensively used and cab called by different
>> > clients as ODBC, JDBC and others.
>>
>> There is no difference between a user-written function and a built-in
>> function in PostgreSQL as regards calling them or their effects. Some
>> clients might offer better support than others, but they should all let
>> you build "raw" sql.
>>
>> --
>> Richard Huxton
>> Archonet Ltd
>>
>
>

On Mon, Sep 15, 2008 at 11:53 AM, c k <shreeseva(dot)learning(at)gmail(dot)com> wrote:
>
>
> If I have a function having:
> begin
> insert into something ...
> select max(primary key) from something..
> end;
>
> does the second statement within a function can view the results after
> execution of first statement to get max(P.K.)?

Yep.

Hello,

If you want to get the value of a autogenerated column it's better to use
"RETURNING"
insert into something returning primary key...

Best Regards,

Rafael Domiciano
Postgres DBA

2008/9/15 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>

> On Mon, Sep 15, 2008 at 11:53 AM, c k <shreeseva(dot)learning(at)gmail(dot)com>
> wrote:
> >
> >
> > If I have a function having:
> > begin
> > insert into something ...
> > select max(primary key) from something..
> > end;
> >
> > does the second statement within a function can view the results after
> > execution of first statement to get max(P.K.)?
>
> Yep.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>