Hi,
When doing an insert with a serial primary key we can refer to currval('sequence_name') in subsequent inserts and we also return it for later processing.
Example:
CREATE TABLE contact (
contactid serial not null primary key, -- creates sequence 'contact_contactid_seq'
firstname text not null,
lastname text
);
CREATE TABLE contactinterests(
contactid int not null references contact(contactid),
interest text
);
-- insert statement as single transaction
INSERT INTO contact(
firstname, lastname)
VALUES('John', 'Smith');
INSERT INTO contactinterests(
contactid, interest)
VALUES (currval('contact_contactid_seq'),'Fishing');
--insert statement as single transaction returning contactid
INSERT INTO contact(
firstname, lastname)
VALUES('John', 'Smith');
INSERT INTO contactinterests(
contactid, interest)
VALUES (currval('contact_contactid_seq'),'Fishing')
returning currval('contact_contactid_seq');
Which is very nice as it gives us back the contactid.
Is it possible to get similar functionality using gen_random_uuid() or uuid-ossp?
Thanks
Robert