Re: how to overwrite tuples in a table

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.
>
>
>
All,

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
poster.

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?

Thanks,
Tim