Re: Calling stored procredure from psycopg2

On Wed, Jan 24, 2018 at 5:48 PM, Graeme <graeme(at)gemmill(dot)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