Re: transactions, serial ids, and JDBC

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;

If your real tables have more fields, you may hit the limit on the number of
parameters allowed in a function call - search the archives to see how you
can change that limit if you need to.

--
Peter Gibbs
EmKel Systems