[Pypgsql-users] Using 'stored procedures' in pyPgSQL

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

I'm trying to check out the more advanced features of PostgreSQL now,
and having some Oracle background, I want to have Stored Procedures.
PostgreSQL doesn't really have the same thing, but after a little bit of
googling, I found that you can have something even better:
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html

So here's my test code to try this out:


# Begin code
from pyPgSQL import PgSQL

db = PgSQL.connect()
cursor = db.cursor()

try:
    cursor.execute("drop table test")
except:
    pass

cursor.execute("create table test(id int, name varchar(20))")
cursor.executemany("insert into test (id, name) values (%s, %s)",
    [(1, 'foo'), (2, 'bar'), (2, 'baz')])

cursor.execute("""create or replace function myfunc(int) returns refcursor as'
    declare
        queried_id alias for $1;
        ref refcursor;
    begin
        open ref for select id, name from test where id="queried_id";
        return ref;
    end;
    ' language 'plpgsql'""")

cursor.callproc("myfunc", 2)                            # (1)
cursorname = cursor.fetchone()[0]                       # (2)
cursor.execute('fetch all in "%s"' % cursorname)        # (3)

for row in cursor.fetchall():
    print row.id, row.name

db.close()
# End code


The lines (1) to (3) feel a little clunky to me. I'd suggest we enhance
pyPgSQL so this feels more natural, and so we can directly use the
PostgreSQL cursors. I have no concrete API in mind, but being able to do
stuff like this would be cool:

cursor.callproc("myfunc", 2)
sp_cursor = cursor.fetchone()[0] # ref. cursor that stored procedure returns
for row in sp_cursor.fetchall():
    # do stuff

With this API, objects of type PG_REFCURSOR would be wrapped to an
instance of Python Cursor class. This cursor class would work similar to
the DB-API2 cursor class and have methods:
    - fetchone()/fetchmany()/fetchall()
    - __iter__() and next() for iteration
    - seek(val, mode="relative") # PEP 0249 optional method,
      unfortunately, PostgreSQL can only do "relative", not "absolute"

Opinions? Can anybody think of a better API? Perhaps it's possible to do
even more magic in the callproc(), so the fetchone()[0] can be avoided
...

Gerhard
-- 
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')))