Transaction in function problem

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: Postgresql Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Transaction in function problem
Date: 2003-05-27 17:51:51
Message-ID: 20030527175150.GA2731@campbell-lange.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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(at)campbell-lange(dot)net>
<www.campbell-lange.net>

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Nabil Sayegh 2003-05-27 18:17:27 Re: Auto increment
Previous Message Fontenot, Paul 2003-05-27 17:37:26 Auto increment