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>
