Thread: Transactions in user defined function

Hi,

Can we use sql transaction(BEGIN, COMMIT, REVOKE) inside a user defined function in Postgresql 8.3?

looking forward to hear from you

Thanks
-Jasid-

--
With warm regards

Jasid Z. A
+91 9946109809

On Fri, Mar 13, 2009 at 12:03 PM, Jasid ZA <za.jasid@gmail.com> wrote:
> Hi,
>
> Can we use sql transaction(BEGIN, COMMIT, REVOKE) inside a user defined
> function in Postgresql 8.3?
>
> looking forward to hear from you

Nope.
If function does something naughty - do RAISE EXCEPTION, that will
break transaction and query (hence rollback).
Please notice, that in plpgsql function uses BEGIN and END, and those
are precisely there to start and end transaction.
You cannot also change transaction level in function, it has to be
done on 'upper' level.

hth.


--
GJ

Jasid ZA wrote:
> Hi,
>
> Can we use sql transaction(BEGIN, COMMIT, REVOKE) inside a user defined
> function in Postgresql 8.3?

Would that part of the documentation help?

http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html

"Functions and trigger procedures are always executed within a
transaction established by an outer query — they cannot start or commit
that transaction, since there would be no context for them to execute in."

so in short: no.

Regards
Tino

Grzegorz Jaśkiewicz wrote:
> On Fri, Mar 13, 2009 at 12:03 PM, Jasid ZA <za.jasid@gmail.com> wrote:
>> Hi,
>>
>> Can we use sql transaction(BEGIN, COMMIT, REVOKE) inside a user defined
>> function in Postgresql 8.3?
>>
>> looking forward to hear from you
>
> Nope.
> If function does something naughty - do RAISE EXCEPTION, that will
> break transaction and query (hence rollback).
> Please notice, that in plpgsql function uses BEGIN and END, and those
> are precisely there to start and end transaction.

Sorry, they are not:

"It is important not to confuse the use of BEGIN/END for grouping
statements in PL/pgSQL with the similarly-named SQL commands for
transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do
not start or end a transaction."

Regards
Tino