Re: Howto explicit cast types for PG-functions?

Martin Lesser wrote:
> Hi,
>
> what is the best way to cast PG-datatypes from the view of my python
> code so a function written in i.e. plpgsql does not claim about wrong
> datatypes?
>
> Background:
>
> When using functions defined in the PG-backend by an execute psycopg
> does not know which type of data the PG-function expects and often casts
> the type incorrect so I get errors. So I would like to cast the type in
> my python-code.
>
> E.g. an error occurs when I use
>
>   cursor.execute('SELECT f_dosomething(%(var)s)', {'var':2})
>
> and the plpgsql-function in the backend is defined as
>
>   CREATE FUNCTION f_dosomething(myvar float8 ...)
>
> Due to the spare documentation of psycopg I did not find an appropriate
> solution.
>
> TIA, Martin
> _______________________________________________
> Psycopg mailing list
> Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
> http://lists.initd.org/mailman/listinfo/psycopg
>
>
>   
Hi Martin,

I'm new to psycopg but I think you are on the right track using the 
pyformat for assigning your variables in the execute statement. As I see 
it , you simply need to list your formatting in the correspondingly 
C-style.

%(var)s  - means format as a valid PostgreSQL formatted string (e.i.: 
'single quote string' )
%(var)f  - means format as a valid PostgreSQL formatted float (e.i.: 3.14 )
%(var)d  - means format as a valid PostgreSQL formatted integer (e.i.: 42)

So you execute command should use the  %(var)f  formatting.

cursor.execute('SELECT f_dosomething(%(var)f)', {'var':2})

Give that a try. I should result in the SQL query looking like this: 
SELECT f_dosomething(2.0000)

As a last resort, you could always build you SQL Query outside of the 
execute statement.
Here are a few an example from the interactive command line.

 >>> sql = "SELECT do_f(%(var)s)" % {'var' : 2}
 >>> print sql
SELECT do_f(2)
 >>> sql = "SELECT do_f(%(var)s)" % {'var' : float(2)}
 >>> print sql
SELECT do_f(2.0)
 >>> sql = "SELECT do_f(%(var)f)" % {'var' :2}
 >>> print sql
SELECT do_f(2.000000)
 >>> sql = "SELECT do_f(%(var)d)" % {'var' :2}
 >>> print sql
SELECT do_f(2)
 >>> cursor.execute(sql)


Hope all this helps,
Chris