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>
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 |