Re: Howto explicit cast types for PG-functions?

"Christopher D. Kyle" <cdkyle-b/Twen6L8IQsA/[email protected]> writes:

> Martin Lesser wrote:
>> 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?
>> ...
>> 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 ...)
>
> 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.

Unfortunately this only works if the PG-function only has one parameter
or all parameters have the same type. So my problem is not a psycopg
issue but affects the use of psycopg.

Try the following in psql:

  CREATE FUNCTION mytest(var1 float8, var2 smallint) RETURNS void AS $$ BEGIN END; $$ LANGUAGE plpgsql;

and then calling
  SELECT mytest(2.0, 2);
gives an error ("function mytest(numeric, integer) does not exist")

> As a last resort, you could always build you SQL Query outside of the
> execute statement.

That's at the moment my only solution combined with an explicit cast in
the query so the query would be called like
  SELECT mytest(2.0::float, 2::smallint);

This IMO ugly necessity seems to be the result of some changes between
PG 8.1 and 8.3, only one example of this change is at the bottom of

http://www.postgresql.org/docs/8.1/static/typeconv-func.html and
http://www.postgresql.org/docs/8.3/static/typeconv-func.html
(compare the difference at the "SELECT substr(1234, 3);" example)

Some former working implicit casts seem to have been gone and the
"Function Type Resolution" described there at the top of the page does
not work as explained (or I misunderstand the description).

Cheers, Martin