Thread: BUG #3376: Erro : COMMIT

The following bug has been logged online:

Bug reference:      3376
Logged by:          Felipe Nogueira
Email address:      fbnogueira@gmail.com
PostgreSQL version: 8.2.4
Operating system:   Windows 2003
Description:        Erro : COMMIT
Details:

PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special).

I have the following function

CREATE OR REPLACE FUNCTION "public"."data" (integer, integer) RETURNS
integer AS
$body$
DECLARE
   vDATA integer;
BEGIN
COMMIT;
END;
$body$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;

When  I execute i have the error

ERROR:  SPI_execute_plan failed executing query "COMMIT":
SPI_ERROR_TRANSACTION
CONTEXT:  PL/pgSQL function "data" line 4 at SQL statement

I saw at Internet this bug was already reported.

Felipe Nogueira wrote:
> I have the following function
>
> CREATE OR REPLACE FUNCTION "public"."data" (integer, integer) RETURNS
> integer AS
> $body$
> DECLARE
>    vDATA integer;
> BEGIN
> COMMIT;
> END;
> $body$
> LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
>
> When  I execute i have the error
>
> ERROR:  SPI_execute_plan failed executing query "COMMIT":
> SPI_ERROR_TRANSACTION
> CONTEXT:  PL/pgSQL function "data" line 4 at SQL statement

You can't commit a transaction inside a function. What were you
expecting to happen?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Please keep the list CC'd so that others can help and learn from the
discussion.

Felipe Nogueira wrote:
> How I commit? I have a function that it will insert into a table, after
> all,
> I have commit.

You commit after calling the function. Like this:

BEGIN;
SELECT data(1,2);
COMMIT;

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com