Re: error in simple sql function breaks connection - Mailing list pgsql-bugs

Fabien COELHO <coelho@cri.ensmp.fr> writes:
> psql> CREATE FUNCTION add_data(TEXT) RETURNS boolean LANGUAGE SQL AS
>        'BEGIN; INSERT INTO foo(data) VALUES($1); COMMIT; SELECT TRUE;';

> psql> SELECT add_data('hello');
> FATAL:  EndTransactionBlock: unexpected state BEGIN
> CONTEXT:  SQL function "add_data" statement 3
> server closed the connection unexpectedly

> Although the "BEGIN" might not be welcome (well, it was ok, possibly
> ignored, with version 7.4),

I'm surprised the COMMIT didn't dump core on you in older versions.
This could never have worked, because if it actually did try to COMMIT
then the function's execution context would have been yanked out from
underneath it.

Anyway, now it says

regression=# SELECT add_data('hello');
ERROR:  cannot begin/end transactions in SQL functions
CONTEXT:  SQL function "add_data" statement 1

            regards, tom lane