Thread: Transaction in function problem

I don't appear to be able to use transactions in functions. Needless to
say, transactions work in psql. The function below works if I take the
TRANSACTION bits out. Help much appreciated!

-----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION
fn_c2b_register_person ( integer ) RETURNS INTEGER
    AS '
DECLARE
    id            ALIAS for $1;
    recone        RECORD;
    rectwo        RECORD;
BEGIN

    IF id IS NULL
    THEN
        RAISE EXCEPTION
            ''no id found at fn_c2b_register_person'';
    END IF;

    BEGIN TRANSACTION;
        UPDATE
            people
        SET
            b_registered = true
        WHERE
            n_id = id;

        SELECT INTO recone
            t_nickname
        FROM
            people
        WHERE
            n_id = id;

        IF NOT FOUND
        THEN
            RAISE EXCEPTION
                ''could not find person at fn_c2b_register_person'';
            ROLLBACK TRANSACTION;
            RETURN 0;
        END IF;

        SELECT INTO rectwo
            n_id
        FROM
            objects
        WHERE
            t_text_id = recone.t_nickname;

        IF FOUND
        THEN
            RAISE EXCEPTION
                ''person object already exists at fn_c2b_register_person'';
            ROLLBACK TRANSACTION;
            RETURN 0;
        END IF;

        INSERT INTO
            objects
            (t_text_id, n_creator, n_type, t_name)
        VALUES
            (recone.t_nickname, id, 2, recone.t_nickname);

        COMMIT TRANSACTION;
    END TRANSACTION;
    RETURN 1;

END;'
    LANGUAGE plpgsql;


--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

Rory,

> I don't appear to be able to use transactions in functions. Needless to
> say, transactions work in psql. The function below works if I take the
> TRANSACTION bits out. Help much appreciated!

That is correct.    Functions include an implied transaction in PostgreSQL,
unless they are part of a larger transaction.   Postgres does not currently
support "nested" transactions, so any attempt to use them inside a function
will fail.

It's on the "TODO" list.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Hi Josh

If it is an implied transaction, can one use a rollback in the body of
the function?

On 27/05/03, Josh Berkus (josh@agliodbs.com) wrote:

> > I don't appear to be able to use transactions in functions. Needless to
> > say, transactions work in psql. The function below works if I take the
> > TRANSACTION bits out. Help much appreciated!
>
> That is correct.    Functions include an implied transaction in PostgreSQL,
> unless they are part of a larger transaction.   Postgres does not currently
> support "nested" transactions, so any attempt to use them inside a function
> will fail.
>
> It's on the "TODO" list.

Thanks
Rory

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>