Jim Archer wrote:
> I have been inserting records into a table using the SQL insert statement
> via the perl5 Pg module. One of the field types is serial, and I have been
> trying to figure out how to get the value that was assigned in the field as
> a result of the insert. The serial typed field is the only one guaranteed
> to be unique, so I can't really do a search, and there are several people
> adding data at once, so I can't reliable guess. Can anyone help?
From the SQL reference manual
-----------------------------
Usage
Create an ascending sequence called serial, starting at 101:
CREATE SEQUENCE serial START 101;
Select the next number from this sequence
SELECT NEXTVAL ('serial');
nextval
-------
114
Use this sequence in an INSERT:
INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing');
Set the sequence value after a COPY FROM:
CREATE FUNCTION distributors_id_max() RETURNS INT4
AS 'SELECT max(id) FROM distributors'
LANGUAGE 'sql';
BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', distributors_id_max());
END;
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Christian Rudow E-Mail: Christian.Rudow@thinx.ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
