[Pypgsql-users] RfC: Inserting data using PostgreSQL functions

[Gerhard <ger...@gm...>]

I've experimented a little with using server-side functions to move SQL
from the client to the server-side. Last time I posted about how to
return cursors from functions and I think I'll try to implement the API
I suggested for pyPgSQL 3.

Now I've tried to come up with a way to get INSERT statements out of
Python sources, too. A function that INSERTS a new record and gives me
back the value of the PRIMARY KEY/SEQUENCE would be ideal. Without the
usual two-step approach of querying the sequence first, then using the
value for the primary key.

This is what my experiments yielded - maybe somebody has comments on
this or other/better approaches?

Btw. is there really no better way than to_char(new_id,
'9999999999999999999999') to convert an int to a varchar?


CREATE TABLE test (
    id serial,
    name varchar(20),
    age int
);

CREATE FUNCTION "test_ins" (varchar[]) RETURNS integer AS '
declare
  new_id integer;
  parms alias for $1;
  i int := 1;
  statement varchar(4000);
begin
  select nextval(''test_id_seq'') into new_id;

  statement := ''insert into test values ('';
  statement := statement || to_char(new_id, ''9999999999999999999999'');
  while parms[i] loop
    statement := statement || '','' || parms[i];
    i := i + 1;
  end loop;
    statement := statement || '');'';

  execute statement;

  return new_id;
end;
' LANGUAGE 'plpgsql';



from pyPgSQL import PgSQL

db = PgSQL.connect()
cursor = db.cursor()
cursor.execute("select test_ins(%s)", ([map(PgSQL._quote, ['Joe Doe', 25])]),)
last_id = cursor.fetchone()[0]
cursor.execute("select * from test where id=%s", (last_id,))
print cursor.fetchone()
db.commit()
db.close()
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))