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

Re: OIDs, CTIDs, updateable cursors and friends

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: OIDs, CTIDs, updateable cursors and friends
Date: 2004-06-04 05:42:08
Message-ID: 87u0xr3mmn.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Shachar Shemesh <psql(at)shemesh(dot)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


In response to

pgsql-hackers by date

Next:From: Christopher Kings-LynneDate: 2004-06-04 06:44:34
Subject: Re: upper() / lower() regression test case needed?
Previous:From: mike gDate: 2004-06-04 05:35:05
Subject: upper() / lower() regression test case needed?

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