Re: OIDs, CTIDs, updateable cursors and friends

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