Re: transactions, serial ids, and JDBC

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?

--Greg

Gregory Seidman sez:
} I've come to the point where I really need to run a transaction. In the
} past it hasn't been as crucial, so I've been happy with individual queries,
} but I am now past that point. I am now trying to insert a row into three
} separate tables, and the rows refer to each other. Two of them have SERIAL
} ids which need to be used as foreign keys. Here's a trimmed down version of
} the tables:
}
} CREATE TABLE A (
}     id SERIAL not null,
}     somedata int not null,
}     primary key (id)
} );
} CREATE TABLE B (
}     id SERIAL not null,
}     moredata int not null,
}     a_id integer not null REFERENCES A(id),
}     primary key (id)
} );
} CREATE TABLE C (
}     b_id integer not null REFERENCES B(id),
}     yetmoredata int not null,
}     primary key (b_id)
} );
}
} The transaction needs to look something like this:
}
} BEGIN
}
} INSERT INTO A (somedata) VALUES (1);
} INSERT INTO B (moredata, a_id) VALUES (1, <id from last insert>);
} INSERT INTO C (yetmoredata, b_id) VALUES (1, <id from last insert>);
}
} END
}
} I don't know how to dependably get the id from the last insert. One
} possibility, I suppose, is to call nextval myself and use the value
} explicitly, but if there is a way to do it portably (i.e. not depending on
} PostgreSQL's specific implementation of a self-incrementing id field) I
} would prefer it.
}
} Oh, one more thing. I'm doing this from JDBC. Can I do transactions with a
} long text string with all of this, or do I need to send each line
} (including BEGIN and END) as a separate Statement? Or is there some better
} way?
}
} --Greg
}
}
} ---------------------------(end of broadcast)---------------------------
} TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org