Thread:
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 Thu, May 20, 2010 at 5:52 PM, <tlange@gwdg.de> 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). -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
> On Thu, May 20, 2010 at 5:52 PM, <tlange@gwdg.de> 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
On 21/05/2010 9:56 AM, Richard Broersma wrote: > On Thu, May 20, 2010 at 5:52 PM,<tlange@gwdg.de> 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). Really? I had problems with Access complaining that the object it just inserted had vanished, because the primary key Access had in memory (null) didn't match what was stored (the generated PK). I had to fetch the next value in the PK sequence manually and store it in Access's record before inserting it to work around this. I wouldn't be surprised if this actually turned out to just require some bizarre ODBC driver parameter change, but I never figured it out and I couldn't find any info about it on the 'net. For the original poster: I posted some information about this at the time I was working on it, so search the archives of this list for MS Access. I eventually ditched Access entirely as the user who was demanding the use of MS Access relented (phew!), so I put together a simple web-app to do what they wanted in a day. Hopefully I'll never need to go near ODBC again, because it's a truly "special" way to talk to PostgreSQL. -- Craig Ringer
On Fri, May 21, 2010 at 1:15 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> Really?
>
> I had problems with Access complaining that the object it just inserted had
> vanished, because the primary key Access had in memory (null) didn't match
> what was stored (the generated PK). I had to fetch the next value in the PK
> sequence manually and store it in Access's record before inserting it to
> work around this.
Trust me, I've felt your pain... In fact, I began to exclusively use
natural primary keys just to avoid this problem.
However, after I've noticed that after 8.3 this problem went away.
Here is a sample of what my postgres log shows:
2010-05-21 07:28:38 PDTLOG: BEGIN; INSERT INTO
"public"."actionitems" ("action","startdate","completiondate") VALUES (E'Test
Action','2010-05-21'::date,'9999-12-31'::date)
2010-05-21 07:28:38 PDTLOG: statement: COMMIT
/* Now MS-Access requeries to find the newly inserted record. But
since we didn't specify the serial field 'itemnbr' MS-Access still
thinks its NULL. */
2010-05-21 07:28:38 PDTLOG: statement: SELECT
"itemnbr","action","startdate","completiondate" FROM "public"."actionitems"
WHERE "itemnbr" IS NULL
/* Here is where MS-Access usually chokes since itemnbr is a serial
and IS NOT NULL. It thinks our serial primary key is null since it
doesn't know know that it can auto-increment. But notice what happens
next that fixes this problem, either this is a new feature of Access
2003 or the >= 8.3 ODBC driver (I'm using pg 8.4 here ). */
2010-05-21 07:28:38 PDTLOG: statement: SELECT "public"."actionitems"."itemnbr"
FROM"public"."actionitems" WHERE "startdate" = '2010-05-21'::date
AND "completio ndate" =
'9999-12-31'::date
/* The table was automatically re-queried to find out what the new
itemnbr actually is according to its default value. And lastly the
former query that failed is re-tried with the newly discovered
itemnbr. */
2010-05-21 07:28:38 PDTLOG: statement: SELECT
"itemnbr","action","startdate","completiondate" FROM "public"."actionitems"
WHERE "itemnbr" = 49
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
A comment on the MS access/PG problem. We experienced this problem as well and I isolated the problem to access's support of timestamp. PG defaults to timestamp(6), While access only supports timestamp(2). When access fills the grid from the table, the values are truncated to ts(2). As the grid is updated, a sql update statement is prepared in the background. all fields except for those updated(by the grid) are used in the construction of the where clause. For most cases this means all of timestamp columns are compared. Ie Update remote x Where local.ts1 = remote.ts1 and ..... The PG timestamp is compared to the local access copy. Because of the truncation no row is found in PG, and Access reports that someone else updated the record. We found the solution to be that all timestamp columns needed to be set to timestamp(0). Once we did this the issue wentaway. Back to the original problem. Serial is just a notational convenience. When created the pg engine will rewrite the statement to 1) Generate a sequence,then 2) create column as int and a default value as nextval(seqName). The datatype remains recorded as Serial,which is not a standard SQL datatype. You can manually do the same yourself - create the sequence, then assign the column default, or easier - create the tableas serial, then change the datatype to int. PG will automatically create the seq and assign the default. Changing the datatype will leave the default and sequence intact. Once datatype is int, Access will recognize it. Doug -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Craig Ringer Sent: Friday, May 21, 2010 3:16 AM To: Richard Broersma Cc: tlange@gwdg.de; pgsql-sql@postgresql.org Subject: Re: [SQL] On 21/05/2010 9:56 AM, Richard Broersma wrote: > On Thu, May 20, 2010 at 5:52 PM,<tlange@gwdg.de> 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). Really? I had problems with Access complaining that the object it just inserted had vanished, because the primary key Access had in memory (null) didn't match what was stored (the generated PK). I had to fetch the next value in the PK sequence manually and store it in Access's record before inserting it to work around this. I wouldn't be surprised if this actually turned out to just require some bizarre ODBC driver parameter change, but I never figured it out and I couldn't find any info about it on the 'net. For the original poster: I posted some information about this at the time I was working on it, so search the archives of this list for MS Access. I eventually ditched Access entirely as the user who was demanding the use of MS Access relented (phew!), so I put together a simple web-app to do what they wanted in a day. Hopefully I'll never need to go near ODBC again, because it's a truly "special" way to talk to PostgreSQL. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
I think I also found one useful switch. In the ODBC-driver's setting there is one "extra" or so field, where 4 possible values can be entered: 0x0, 0x1, 0x2... If I remember right with 0x2 the driver mimics an SQL-Server auto increment, which Access acknowledges. However, does anyone knows good resources to learn more about triggers? Can also be a book to buy. I need an after insert/update trigger to update a PostGIS geometry column of a table after someone has typed in a x,y-location into x and y columns. Thx,Torsten Am Freitag, 21. Mai 2010 17:23:12 schrieb Little, Douglas: > A comment on the MS access/PG problem. > > We experienced this problem as well and I isolated the problem to access's > support of timestamp. PG defaults to timestamp(6), While access only > supports timestamp(2). > > When access fills the grid from the table, the values are truncated to > ts(2). > > As the grid is updated, a sql update statement is prepared in the > background. all fields except for those updated(by the grid) are used in > the construction of the where clause. For most cases this means all of > timestamp columns are compared. Ie Update remote x > Where local.ts1 = remote.ts1 and ..... > The PG timestamp is compared to the local access copy. > Because of the truncation no row is found in PG, and Access reports that > someone else updated the record. > > We found the solution to be that all timestamp columns needed to be set to > timestamp(0). Once we did this the issue went away. > > Back to the original problem. > Serial is just a notational convenience. When created the pg engine will > rewrite the statement to 1) Generate a sequence, then 2) create column as > int and a default value as nextval(seqName). The datatype remains > recorded as Serial, which is not a standard SQL datatype. > > You can manually do the same yourself - create the sequence, then assign > the column default, or easier - create the table as serial, then change the > datatype to int. PG will automatically create the seq and assign the > default. Changing the datatype will leave the default and sequence intact. > > Once datatype is int, Access will recognize it. > > Doug > > > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Craig Ringer Sent: > Friday, May 21, 2010 3:16 AM > To: Richard Broersma > Cc: tlange@gwdg.de; pgsql-sql@postgresql.org > Subject: Re: [SQL] > > On 21/05/2010 9:56 AM, Richard Broersma wrote: > > On Thu, May 20, 2010 at 5:52 PM,<tlange@gwdg.de> 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). > > Really? > > I had problems with Access complaining that the object it just inserted > had vanished, because the primary key Access had in memory (null) didn't > match what was stored (the generated PK). I had to fetch the next value > in the PK sequence manually and store it in Access's record before > inserting it to work around this. > > I wouldn't be surprised if this actually turned out to just require some > bizarre ODBC driver parameter change, but I never figured it out and I > couldn't find any info about it on the 'net. > > For the original poster: I posted some information about this at the > time I was working on it, so search the archives of this list for MS > Access. > > I eventually ditched Access entirely as the user who was demanding the > use of MS Access relented (phew!), so I put together a simple web-app to > do what they wanted in a day. Hopefully I'll never need to go near ODBC > again, because it's a truly "special" way to talk to PostgreSQL. > > -- > Craig Ringer >