Hi all,

I'm in the process of writing an OLE DB provider for postgres. I am, 
right now, at the point where updating an entry becomes an issue. 
Ideally, I would open an updateable cursor for a table/view, and use 
that. Unfortunetly, Postgres doesn't seem to support those at all.

As an alternative, I was thinking of using some unique field to 
identify, with certanity, the line in question. This will allow me to 
use an "update" command to modify said line, in case there is a need 
with the OLE DB client. My quetion is this - what can I use?

I have tried to find some docs regarding OIDs and CTIDs, but the docs 
seem scarce. If I understand this correctly - OID is meant to identify a 
row in a table, though it is not guarenteed to be unique across tables, 
or even inside a given table. A CTID is meant to identify the physical 
location at which a row is stored. Are these correct?

Would adding "OID" to the rows returned by each "Select" call, and then 
doing "update blah where oid=xxx" when I'm requested to update the row 
sound like a reasonable stategy, in lieu of updateable cursors? Can 
anyone suggest a better way?

                Shachar

-- 
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/


Wed, 18 Feb 2004 15:17:34 +0200
Shachar Shemesh <psql@shemesh.biz>
OIDs, CTIDs, updateable cursors and friends
Shachar Shemesh <psql@shemesh.biz> writes:

> Would adding "OID" to the rows returned by each "Select" call, and then doing
> "update blah where oid=xxx" when I'm requested to update the row sound like a
> reasonable stategy, in lieu of updateable cursors? Can anyone suggest a better
> way?

If you're in control of the database schema and can ensure that all tables
will have OIDs enabled and you can add a unique index on OID on all these
tables then yes. But it's not ideal. If OID wraps around you'll get errors
from unique key violations.

A better strategy is to pull the primary key columns from information_schema
and use those columns. This would be more work but would work on any table
with a primary key.

This won't work for tables without primary keys, but in that case, arguably,
updating records doesn't really make sense anyways.

Something like this, though I'm not really very familiar with the
information_schema. 

db=> SELECT ordinal_position,column_name 
       FROM information_schema.table_constraints AS a 
       JOIN information_schema.key_column_usage AS b USING (constraint_schema,constraint_name) 
      WHERE a.constraint_type = 'PRIMARY KEY' 
        AND a.table_schema = 'public' 
        AND a.table_name = 'country' 
      ORDER BY ordinal_position;
 ordinal_position | column_name  
------------------+--------------
                1 | country_code
(1 row)


-- 
greg


04 Jun 2004 01:42:08 -0400
Greg Stark <gsstark@mit.edu>
Re: OIDs, CTIDs, updateable cursors and friends