Обсуждение: SPI_ERROR_TRANSACTION [PostgreSQL 8.3]

Hi

As a newbie in PL/pgSQL I have faced an error SPI_ERROR_TRANSACTION which
was raised always when I have tried to launch following function with
uncommented keywords SAVEPOINT, SET TRANSACTION and COMMIT(when commented,
function works fine):

CREATE OR REPLACE FUNCTION insert_employee(
name_emp varchar, surname_emp varchar, street_emp varchar, postcode_emp
varchar, city_emp varchar, login_emp varchar, password_emp varchar)
returns boolean as $insert_emp$

DECLARE
    last_row_id integer;

BEGIN

    --SAVEPOINT my_savepoint;
    --SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    INSERT INTO person VALUES (nextval('person_seq'), $1,$2,$3,$4,$5);
    IF NOT FOUND THEN
        ROLLBACK TO SAVEPOINT my_savepoint;
        RAISE EXCEPTION 'New person insertion error';
        RETURN false;
    END IF;

    SELECT currval('public.person_seq') INTO last_row_id;

    INSERT INTO employee VALUES (nextval('employee_seq'), last_row_id, $6, $7);
    IF NOT FOUND THEN
        ROLLBACK TO SAVEPOINT my_savepoint;
        RETURN false;
    END IF;
    --COMMIT;

    RETURN true;

END;
$insert_emp$ LANGUAGE plpgsql;

I don't know why these keywords cannot be used in function block if they
works fine in Begin-Commit block. How can I change TRANSACTION type into
SERIALIZABLE within mentioned insert_employee function.

Thanks in advance,
Daroslav

--
View this message in context: http://www.nabble.com/SPI_ERROR_TRANSACTION--PostgreSQL-8.3--tp23277399p23277399.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Daroslav wrote:
>
> Hi
>
> As a newbie in PL/pgSQL I have faced an error SPI_ERROR_TRANSACTION which
> was raised always when I have tried to launch following function with
> uncommented keywords SAVEPOINT, SET TRANSACTION and COMMIT(when commented,
> function works fine):

Yes, you cannot use those constructs in plpgsql (or any other PL for
that matter; it's a limitation of the underlying layer).  You need to
use BEGIN/EXCEPTION/END blocks instead.

Also, keep in mind that if you RAISE EXCEPTION, the current transaction
is always rolled back, so the RETURN FALSE in your function would never be
reached.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.