Thread: define transaction within pg/psql. Necessary?

Hello,

if I define a pg/pgsql function, and I call that outside a transaction,
does it create one for itself? Or should I add BEGIN and COMMIT statements within
the function?

Thanks,
Antonio.

all statements in postgresql are self contained transactions, and you cannot change that.
To answer your question directly, you don't have to, it will all be a transaction.

The best example of that is to run following query in psql:

CREATE TEMP TABLE foo() ON COMMIT DROP;


the table will not exists anymore after running it. Precisely because it was 'automatically' wrapped in begin/commit, and dropped at the end of it.


hth

On 18/02/10 10:02, Antonio Goméz Soto wrote:
>
> if I define a pg/pgsql function, and I call that outside a transaction,
> does it create one for itself? Or should I add BEGIN and COMMIT
> statements within
> the function?

You can't call a function outside a transaction. Every statement in
PostgreSQL is inside a transaction, either one you define yourself, or
an implicit one that just lasts for the duration of one statement.

--
   Richard Huxton
   Archonet Ltd

Op 18-02-10 11:07, Richard Huxton schreef:
> On 18/02/10 10:02, Antonio Goméz Soto wrote:
>>
>> if I define a pg/pgsql function, and I call that outside a transaction,
>> does it create one for itself? Or should I add BEGIN and COMMIT
>> statements within
>> the function?
>
> You can't call a function outside a transaction. Every statement in
> PostgreSQL is inside a transaction, either one you define yourself, or
> an implicit one that just lasts for the duration of one statement.
>


Clear answer. Thanks.

Antonio