CODEWITHSUNDEEP
postgresqlpython-2.7psycopg2
Teboto
Teboto

Reputation: 1225

String passed into cursor.callproc becomes unknown (psycopg2, python 2.7, postgres 9.3)

For some reason, passing a string from Python into a Postgres function and calling it using a psycopg2 cursor causes the function to be unrecognized because the argument is unknown.

The function is very simple like so:

CREATE OR REPLACE FUNCTION text_test (some_text text)
RETURNS void AS $$
BEGIN
END;
$$ LANGUAGE plpgsql;

On the python side, I have:

cursor.callproc("text_test", ("test",))

And I get the following error:

psycopg2.ProgrammingError: function text_test(unknown) does not exist
LINE 1: SELECT * FROM text_test('test')
                      ^
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Why does this only happen with strings and what do I need to do to have a function successfully accept a string? For some reason numeric data types are unaffected by this problem.

Upvotes: 2

Views: 3735

Answers (2)

Andrei Dobre
Andrei Dobre

Reputation: 103

You could also make a list with the parameters you need to send:

param_list = ["test"]
curs.callproc(proc_name, param_list)

Here is a good answer about it: python + psycopg2 = unknown types?

Upvotes: 2

fog
fog

Reputation: 3391

This happens because there is no way to cast the string to the "correct" text type. Is it a char(N)? A varchar(N)? A text?

Unfortunately .callproc() doesn't provide an easy way to specify the argument types but you can always use .execute() casting the arguments explicitly and everything works:

curs.execute("SELECT * FROM text_test(%s::text)", ("test",))

Upvotes: 4

Related Questions