[SQL] 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 select nextval($); RETURN pid_num; END; ' LANGUAGE 'plpgsql'; -- and how will the trigger looks like ??? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL]
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 ??? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[Fwd: Re: [SQL] sorry, now with subject... trigger & nextval(seq)]
> On Thursday 20 May 2010 5:53:51 pm [email protected] wrote: >> 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". >> > > You know serial is just a shortcut for: > > pid int NOT NULL DEFAULT nextval('seq') yes. sorry datatype quoted was bit ambiguous. > I think you will find Access will place nice if you use the long form to > define > your autoincrement. > Ok, I'll give it a try! Thank you, Torsten -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sorry, now with subject... trigger & nextval(seq)
> On Thu, May 20, 2010 at 5:52 PM, wrote: > >> 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". > > Uh? Access doesn't need to acknowledge the serial datatype. > At-least in recent versions auto increment is recognized by MS-Access > just fine (I'm guessing this is due to the Return clause which the > ODBC driver automatically calls). > Thanks Richard. Ok, I hope our Access version is not too old (97 or 2000 I think) because I could not insert. I'll try the most recent ODBC driver and follow also Adrians suggestion. Torsten -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
