callproc, string formatting

Hi all,

I haven't used psycopg2 since 2006 or so, and didn't use it to call server
side functions, so I'm a bit out of phase and could use some clarification:

I need to call a server side function called 'myapp.new_user'. My first
attempt looked something like this:

--------------------------- snip ---------------------------
qdict = {'fname': self.fname, 'lname': self.lname, 'dob': self.dob, 'city':
self.city, 'state': self.state, 'zip': self.zipcode}

sqlcall = """SELECT * FROM myapp.new_user( %(fname)s, %(lname)s,
%(dob)s, %(city)s, %(state)s, %(zip)s""" % qdict

curs.execute(sqlcall)

--------------------------- /snip ---------------------------
That fails because none of the strings are not quoted at all, ever.

Of course, I foolishly tried going back and putting quotes around all of
those named string formatting args, and of course that fails when you have
something like a quoted "NULL" trying to move into a date column (dob is
allowed to be null). It has other issues too, like being error-prone and a
PITA, but hey, it was pre-coffee time.

My next attempt looked like this:
--------------------------- snip ---------------------------
curs.execute("""SELECT * FROM myapp.new_user( %(fname)s, %(lname)s,
%(dob)s, %(city)s, %(state)s, %(zip)s""", qdict)
--------------------------- /snip ---------------------------

Effectively just replacing the "%" with a "," to pass an actual param list
instead of passing only one arg that contains a string formatting operation.


But this winds up with unquoted strings as well.

So I looked into callproc a bit, but my first attempt isn't looking too
promising:
--------------------------- snip ---------------------------
# you can't pass a dict to callproc
callproc_params = [self.fname, self.lname, self.dob, self.city, self.state,
self.zip]
curs.callproc('myapp.new_user', callproc_params)
--------------------------- /snip ---------------------------

The error I get is:

"function myapp.new_user(unknown, unknown, unknown, unknown, unknown,
unknown) does not exist
HINT: No function matches the given name and argument types. You might need
to add explicit type casts"

Can anyone help me understand the right way to do this, or point me to
documentation on how to perform this type of operation?

thanks.
brian
-- 
Brian K. Jones
Python Magazine  http://www.pythonmagazine.com
My Blog          http://www.protocolostomy.com

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg