Обсуждение: Functions

Поиск
Список
Период
Сортировка

Functions

От
"c k"
Дата:
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

Re: [ADMIN] Functions

От
"Scott Marlowe"
Дата:
On Mon, Sep 15, 2008 at 10:55 AM, c k <shreeseva.learning@gmail.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.

Re: Functions

От
"c k"
Дата:


On Mon, Sep 15, 2008 at 11:22 PM, c k <shreeseva.learning@gmail.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@archonet.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


Re: [ADMIN] Functions

От
"Scott Marlowe"
Дата:
On Mon, Sep 15, 2008 at 11:53 AM, c k <shreeseva.learning@gmail.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.

Re: [ADMIN] Functions

От
"Rafael Domiciano"
Дата:
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.marlowe@gmail.com>
On Mon, Sep 15, 2008 at 11:53 AM, c k <shreeseva.learning@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin