Thread: sorry, now with subject... trigger & nextval(seq)
Hello all, I'm new to triggers in PostgreSQL. I have to create a trigger on insert to increment a sequence to overcome MS-Access' limitation in acknowledging serial "datatype". Could anyone put me on right track? I was looking the whole day for references on that... Years ago I quickly found a reference how to do it in Oracle. However, I could not find anything that explained how to do this in postgresql... I think, it should go the direction see below... But how exactly :-/ ? Many thanks for any help, Torsten create table testtab ( pid bigint, sometext text ); create sequence test; -- hmm something like this...? CREATE FUNCTION count_up (varchar) RETURNS opaque AS ' DECLARE pid_num bigint; BEGIN select into pid_num from selectnextval($); RETURN pid_num; END; ' LANGUAGE 'plpgsql'; -- and how will the trigger looks like ???
On Thursday 20 May 2010 5:53:51 pm tlange@gwdg.de wrote: > Hello all, > > I'm new to triggers in PostgreSQL. I have to create a trigger on insert to > increment a sequence to overcome MS-Access' limitation in acknowledging > serial "datatype". > > Could anyone put me on right track? I was looking the whole day for > references on that... Years ago I quickly found a reference how to do it > in Oracle. However, I could not find anything that explained how to do > this in postgresql... I think, it should go the direction see below... But > how exactly :-/ ? > > > Many thanks for any help, Torsten > > > create table testtab ( > pid bigint, > sometext text > ); > > create sequence test; > > -- hmm something like this...? > CREATE FUNCTION count_up (varchar) RETURNS opaque AS ' > DECLARE > pid_num bigint; > BEGIN > select into pid_num from select nextval($); > RETURN pid_num; > END; > ' LANGUAGE 'plpgsql'; > > -- and how will the trigger looks like > ??? You know serial is just a shortcut for: pid int NOT NULL DEFAULT nextval('seq') I think you will find Access will place nice if you use the long form to define your autoincrement. -- Adrian Klaver adrian.klaver@gmail.com
Am 21.05.2010 03:46, schrieb Adrian Klaver: > You know serial is just a shortcut for: > > pid int NOT NULL DEFAULT nextval('seq') > > I think you will find Access will place nice if you use the long form to define > your autoincrement. > Not true. Serial will also add an internal dependency between the sequence, will create the sequence and will drop everything if the column gets dropped. Regards, Daniel Migowski