Transactions in functions ( was Re: transactions, serial ids, and JDBC)

On 8/8/02 1:56 AM, "Peter Gibbs" <peter(at)emkel(dot)co(dot)za> wrote:

> Gregory Seidman wrote:
>
>> On second thought, is there any reason not to put the whole transaction
>> into a function? Will it still act as a transaction? And do I have to use
>> plpgsql or is there a way to store a variable (i.e. the ids I need) using
>> straight SQL?
>
> A function will always be executed within transaction context. You don't
> actually need any variables for this:
>
> create function abc(int,int,int) returns int as '
> insert into a (somedata) values ($1);
> insert into b (moredata, a_id) values ($2, currval(''a_id_seq''));
> insert into c (yetmoredata, b_id) values ($3, currval(''b_id_seq''));
> select currval(''a_id_seq'')::int;
> ' language sql;
>

I have a function that needs to do several things and roll it all back if
any element fails. I wrote it like this:

CREATE FUNCTION
transfer_student(integer,,character,,character,,character,,integer) RETURNS
int4 AS '
BEGIN;
UPDATE iep_student SET id_county = $2, id_district = $3, id_school = $4,
id_case_mgr = 0, id_list_team='' WHERE id_student = $1;
UPDATE iep_student_team SET status='Inactive' WHERE id_student = $1;
UPDATE iep_transfer_request SET transfer_type='Confirmed' where
id_transfer_request = $5;
COMMIT;
SELECT id_student from iep_student where id_student = $1;
' LANGUAGE 'sql';

I believe I read elsewhere that transactional logic doesn't work or doesn't
apply in a function. And the message above seems to imply that the function
will behave transactionally without explicit begin/commit.

So what do I need to do, or not do, in order that the function executes the
way I intend, that all three UPDATES will succeed or fail?

Thanks,

Steve