传递给cursor.callproc的字符串变为未知（psycopg2，python 2.7，postgres 9.3） [英] String passed into cursor.callproc becomes unknown (psycopg2, python 2.7, postgres 9.3)

问题描述

由于某种原因，将字符串从Python传递到Postgres函数中，并使用psycopg2游标调用该函数会导致该函数无法识别，因为参数未知。

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;

在python方面，我有：

On the python side, I have:

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

我得到以下错误：

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.

推荐答案

之所以会发生这种情况，是因为无法将字符串强制转换为正确的文本类型。它是 char（N）吗？ varchar（N）吗？ 文本？

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?

不幸的是， .callproc（）没有提供简单的方法来指定参数类型，但您始终可以使用 .execute（）显式转换参数，一切正常：

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",))

这篇关于传递给cursor.callproc的字符串变为未知（psycopg2，python 2.7，postgres 9.3）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！