When importing a CSV file using Laravel I found that I’d get some strange error messages relating to a called function not matching the parameters that I was sending.
local.ERROR: SQLSTATE: Undefined function: 7 ERROR: function st_makepoint(numeric, numeric) does not exist LINE 1: SELECT ST_SetSRID(ST_MakePoint(NEW.x_coordinate, NEW.y_coord... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
I narrowed it down to the
ST_MakePoint() functions and was 100% sure that they existed in the database, and with the right parameter types. I tried casting the values to
double precision and still and the same errors.
The solution turned out to be that the function existed in the
public schema NOT the schema I was using!
This seems strange because my database parameters has
public in the
search_path parameter. What was strange is that when I used the same import file directly in pgadmin it ran the same insert trigger and worked just fine. It was only when I ran the import from the web server that the functions couldn’t be found. But they are in the same trigger!?
I then modified the trigger to specify the schema to use eg.
That is all! So the
search_path only seems valid at the server and not within my PHP session.