PostgreSQL, psycopg2 and OID-less tables

[GHUM]

Dale,

> Now that OIDs have been deprecated in PostgreSQL, how do you find the key of
> a newly inserted record?

using OIDs as primary key was no good idea for some PostgreSQL versions
allready ... i.e. they really make dump & restore much more
challenging.

So usually us have something along:

CREATE TABLE feuser
(
  id_user serial NOT NULL,
  name text,
  CONSTRAINT feuser_pkey PRIMARY KEY (id_user),
  CONSTRAINT feuser_name_key UNIQUE (name)
)
WITHOUT OIDS;

which automatically creates a sequence for you and rewrites the serial
"id_user" with a default of

nextval('feuser_id_user_seq'::regclass)

So, to get the key of a record inserted, basically there are two
methods. (from now on "cs" is a DB-API 2.0 compliant cursor object,
i.e. from psycopg2

cn=psycopg2.connect(....)
cs=cn.cursor()

a) get id first, then insert
cs.execute("select nextval('feuser_id_user_seq'::regclass)")
newid=cs.fetchone()[0]

cs.execute("insert into feuser (id_user, name) values (%(id_user)s,
%(name)s)",
             dict(id_user=newid, name="Karl Napf")

cs.commit()
-> now newid contains your new id.

b) create a serverside function on PostgreSQL:
CREATE OR REPLACE FUNCTION insfeuser (text)
  RETURNS integer AS
$BODY$
        DECLARE
            result int4;
        BEGIN

           select nextval('feuser_id_user_seq'::regclass) into result;
  	   insert into feuser (id_user, name) values (result, $1);

     RETURN result;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

within python:
cs.execute("select insfeuser(%(name)s)",  dict(name="Karl Napf"))
newid=cs.fetchone()[0]

Hope that helps,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607