python + psycopg2 = unknown types?

Question

It seems when I use callproc(), psycopg2 isn't properly casting strings as text or character varying.

For example:

values = [pid, 4, 4, 'bureau ama', 0, 130, row['report_dte'], row['report_dte'], 1, 1, 1, None, None, 'published', row['report_dte']]
cur.callproc('header', values)

Yields:

psycopg2.ProgrammingError: function header(integer, integer, integer, unknown, integer, integer, unknown, unknown, integer, integer, integer, unknown, unknown, unknown, unknown) does not exist
LINE 1: SELECT * FROM header(509952,4...

First, the "bureau ama" is unknown instead of text/character, and row["report_dte"] are all unknown, where they are date types in the database, and datetime.date objects in python.

Any idea? Using python 2.6.5. Using cur.mogrify(), the query looks like:

SELECT header(509952,4,4,E'bureau ama',0,130,'2011-01-24','2011-01-24',1,1,1,NULL,NULL,E'published','2011-01-24')

Not sure what the E'bureau pitcher ama' means...

Answer 1

Based on your updated mogrify() output, your strings and datetimes appear to be correctly interpreted. The E'foo bar' is Postgres' "escape string constant". It lets you represent C-style escape sequences, like \t for tab, in text. Also the unknown you see in the psycopg2.ProgrammingError are nothing to worry about, that is normal behavior. You might first check that the number of arguments to your function call are correct, and perhaps try calling the procedure with a hand-written params to identify which param might be causing a problem:

Test procedure:

CREATE OR REPLACE FUNCTION
    foo (num INTEGER, name VARCHAR, ts TIMESTAMP)
RETURNS TABLE (num INTEGER, name VARCHAR, ts TIMESTAMP)
AS $$ SELECT $1, $2, $3; $$
LANGUAGE SQL;

Example:

% python
>>> import datetime
>>> import psycopg2
>>> conn = psycopg2.connect("user=postgres")
>>> r = conn.cursor()
>>> args = [1, "hello", datetime.datetime.now()]
>>> r.callproc('foo', args)
[1, 'hello', datetime.datetime(2011, 3, 10, 18, 51, 24, 904103)]

>>> r.callproc('fooxyz', args)
psycopg2.ProgrammingError: function fooxyz(integer, unknown, unknown) does not exist

LINE 1: SELECT * FROM fooxyz(1,E'hello','2011-03-10T18:51:24.904103'...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.