Skip site navigation (1) Skip section navigation (2)

Re: how to overwrite tuples in a table

From: Tim Pushor <timp(at)crossthread(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Oliver Fromme <olli(at)lurza(dot)secnetix(dot)de>,"Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar(dot)Pyatalo(at)honeywell(dot)com>,pgsql-novice(at)postgresql(dot)org
Subject: Re: how to overwrite tuples in a table
Date: 2004-09-10 18:15:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Josh Berkus wrote:

>You are correct.    However, he can do it right now the other way around, if 
>it can be nested into a plpgsql function or done with libpq:  do an UPDATE, 
>check the number of rows affected, and if it's 0, do an insert.
>By 8.1/8.2 we'll likely have implemented the new SQL spec for this sort of 
>operation, and this common problem will go away.
>Mind you, it's not a problem I've ever personally had.   I'm actually a bit 
>puzzled about how the application could NOT know whether it's handling a new 
>or a modified row; makes me wonder about people's application design.

This is a very timely thread! I was just thinking about the exact same 
thing. As for why we would want to do that, I'll explain my situation.

I have an old BASIC compiler/interpreter that uses AlphaMicro compatible 
ISAM databases. I have a customer that has for the past 10 years been 
writing modules into this system, and it runs pretty much every aspect 
of their business. Now they would really like  to be able to access the 
data from outside of the BASIC environment (ODBC access would be 
wonderful). This customer owns both the source code for the BASIC 
interpreter & compiler and the business sysytem.

During my stint here I have been porting the system to Linux and fixing 
some obscure bugs in the ISAM section. After figuring out how the ISAM 
subsystem works, I am thinking that I can hook into both the WRITE block 
and ISAM delete parts, pass the data off to another process, and have it 
insert or delete data from the relational database. This way I'd be able 
to have a read-only mirror of the data in a relational database for 
customers to access. The problem is, I can't (easily/reliably) 
differentiate the difference between a WRITE to a new block (an insert) 
or an existing one (update), hence my exact same problem as the original 

Unfortunately I have only really ever done simple things in SQL 
database, so I'm kinda green.

Dumb question: could the plpgsql function that you mentioned be 
implemented as a trigger as to remain transparent to my bridge?


In response to

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2004-09-11 05:31:50
Subject: Re: forcing date ordering
Previous:From: Josh BerkusDate: 2004-09-10 17:33:37
Subject: Re: how to overwrite tuples in a table

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group