Обсуждение: Calling stored procredure from psycopg2

Environment is Mageia 5 x86_64, Python 3.4, Postgresql 9.3.20, 
psycopg2-2.7.1

I have a working application that calls an external sql stored procedure 
called search.sql with entry point "search_columns". I access it with 
the statement self.cursor.callproc('search_columns', (target,))

I have moved that application to the following environment: Mageia 6 
x86_64, Python 3.5, Postgresql 9.6.6, psycopg2-2.7.3.2. I now get an 
error message:

psycopg2.ProgrammingError: function search_columns(unknown) does not exist

search.sql is in the same directory as other Python modules, so there 
seems to have been a change in how to call stored sql procedures. Can 
someone help please?

Thanks

Graeme

On Tue, Jan 23, 2018 at 02:00:57PM +0000, Graeme Gemmill wrote:
> Environment is Mageia 5 x86_64, Python 3.4, Postgresql 9.3.20,
> psycopg2-2.7.1
> 
> I have a working application that calls an external sql stored procedure
> called search.sql with entry point "search_columns". I access it with the
> statement self.cursor.callproc('search_columns', (target,))
> 
> I have moved that application to the following environment: Mageia 6 x86_64,
> Python 3.5, Postgresql 9.6.6, psycopg2-2.7.3.2. I now get an error message:
> 
> psycopg2.ProgrammingError: function search_columns(unknown) does not exist
> 
> search.sql is in the same directory as other Python modules, so there seems
> to have been a change in how to call stored sql procedures. Can someone help
> please?

That error message implies that PostgreSQL doesn't know about the
input type.  Maybe this needs to be done in raw SQL.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, Jan 23, 2018 at 2:00 PM, Graeme Gemmill <graeme@gemmill.name> wrote:
> Environment is Mageia 5 x86_64, Python 3.4, Postgresql 9.3.20,
> psycopg2-2.7.1
>
> I have a working application that calls an external sql stored procedure
> called search.sql with entry point "search_columns". I access it with the
> statement self.cursor.callproc('search_columns', (target,))
>
> I have moved that application to the following environment: Mageia 6 x86_64,
> Python 3.5, Postgresql 9.6.6, psycopg2-2.7.3.2. I now get an error message:
>
> psycopg2.ProgrammingError: function search_columns(unknown) does not exist
>
> search.sql is in the same directory as other Python modules, so there seems
> to have been a change in how to call stored sql procedures. Can someone help
> please?

Most likely you are connecting to the wrong database, or to a database
where the function `search_columns()` is not in the `search_path`.

-- Daniele

On 23/01/18 14:56, Daniele Varrazzo wrote:
> On Tue, Jan 23, 2018 at 2:00 PM, Graeme Gemmill <graeme@gemmill.name> wrote:
>> Environment is Mageia 5 x86_64, Python 3.4, Postgresql 9.3.20,
>> psycopg2-2.7.1
>>
>> I have a working application that calls an external sql stored procedure
>> called search.sql with entry point "search_columns". I access it with the
>> statement self.cursor.callproc('search_columns', (target,))
>>
>> I have moved that application to the following environment: Mageia 6 x86_64,
>> Python 3.5, Postgresql 9.6.6, psycopg2-2.7.3.2. I now get an error message:
>>
>> psycopg2.ProgrammingError: function search_columns(unknown) does not exist
>>
>> search.sql is in the same directory as other Python modules, so there seems
>> to have been a change in how to call stored sql procedures. Can someone help
>> please?
David, Daniele: thank you for replying.
David: the search routine works correctly in one environment, so I don't 
think raw SQL is the answer
Daniele: What would be the 'search_path'? As I said, 'search.sql' is in 
the same directory as other Python modules used by the application, and 
no path variable was set when I first copied  the code from Stack 
Exchange and tested it.
I can find no references to path/environment in Postgresql's 
documentation of pl/pgsql.
Regards
Graeme

On 23/01/18, Graeme Gemmill (graeme@gemmill.name) wrote:
> Daniele: What would be the 'search_path'? 

Each database can have several schemas. To access schemas outside of the
default 'public' schema, you need to set the search_path.

https://www.postgresql.org/docs/current/static/runtime-config-client.html

If you are trying to use functions or data outside of the public schema
you can normally do so by either running something like

    dbcur.execute('set search_path = %s' % self.search_path)

or fully qualify the target function or object in your query, eg:

    select * from myschema.fn_myfunction(myschema.toys)

Rory

Ed, Rory: thank you for replying. However, I do not think the postgresql 
'search_path' is involved. I used psql to input the function, display 
the search path
show search_path;
    search_path
-----------------
  "$user", public
and \df to confirm its presence:
Schema |      Name      |
    public | search_columns |  etc
I still get
psycopg2.ProgrammingError: function search_columns(unknown) does not 
exist. (Yes, I did restart the server)
Furthermore, I didn't have to do anything in the Mageia 5 environment to 
install search.sql, just copied it to the same directory as the main 
Python routine.
The search routine I use is copied from
https://stackoverflow.com/questions/5350088/how-to-search-a-specific-value-in-all-tables-postgresql/39473673
Furthermore:
In the Mageia 5 implementation, the psql command:
contacts=# select * from search_columns('Clarke');
produces the correct response. The Mageia 6 implementation produces the 
error message:
ERROR:  function search_columns(unknown) does not exist

Regards to all,
Graeme

On Wed, Jan 24, 2018 at 5:48 PM, Graeme <graeme@gemmill.name> wrote:
> Ed, Rory: thank you for replying. However, I do not think the postgresql
> 'search_path' is involved. I used psql to input the function, display the
> search path
> show search_path;
>    search_path
> -----------------
>  "$user", public
> and \df to confirm its presence:
> Schema |      Name      |
>    public | search_columns |  etc
> I still get
> psycopg2.ProgrammingError: function search_columns(unknown) does not exist.
> (Yes, I did restart the server)
> Furthermore, I didn't have to do anything in the Mageia 5 environment to
> install search.sql, just copied it to the same directory as the main Python
> routine.
> The search routine I use is copied from
> https://stackoverflow.com/questions/5350088/how-to-search-a-specific-value-in-all-tables-postgresql/39473673
> Furthermore:
> In the Mageia 5 implementation, the psql command:
> contacts=# select * from search_columns('Clarke');
> produces the correct response. The Mageia 6 implementation produces the
> error message:
> ERROR:  function search_columns(unknown) does not exist

Graeme,

the presence of the file 'search.sql' is confusing you. Psycopg
doesn't use that file: it just connects to a server and execute the
function that must be already there (stored procedure means that: it
is a procedure stored and running in the server, as opposite as being
code living on the client; if you are not familiar with this concept I
suggest reading about it on the PostgreSQL documentation).

Psycopg is reporting that the search_column function doesn't exist in
the server it is connecting to. You have to run the 'search.sql' file
into the server in installation phase, for instance using "psql -f
search.sql", after which the psycopg code will work as expected.

-- Daniele

On 24/01/18 18:22, Daniele Varrazzo wrote:
>
> Psycopg is reporting that the search_column function doesn't exist in
> the server it is connecting to. You have to run the 'search.sql' file
> into the server in installation phase, for instance using "psql -f
> search.sql", after which the psycopg code will work as expected.
>
>
Thank you. That worked (but I don't recall doing anything similar on the 
Mageia 5 installation!)
Regards
Graeme