PG::UndefinedFunction: ERROR: function get_nearest_vertex_to_lon_lat(double precision, double precision) does not exist

Question

I'm having a weird problem, I created a custom SQL function based on this suggestion. I have a model Route with a controller action create that calls this method on the model

def get_route(startx, starty, endx, endy)

  query = "SELECT seq, cost, slope::double precision, designacao::character varying(192), length::double precision," +
    "ST_AsGeoJSON(geom) FROM pgr_dijkstra('" +
      "SELECT gid AS id," +
               "source::integer," +
               "target::integer," +
               "length AS cost " + 
              "FROM ways WHERE CAST(classif as int) <> 2'," +
      "get_nearest_vertex_to_lon_lat(CAST(#{startx} as float) , CAST(#{starty} as float)), " + 
      "get_nearest_vertex_to_lon_lat(CAST(#{endx} as float), CAST(#{endy} as float)), false, false) a LEFT JOIN ways b ON (a.id2 = b.gid);"

  results = ActiveRecord::Base.connection.execute(query)
  return results
end

This works on development but doesn't work on production, it gives an PG::UndefinedFunction: ERROR: function get_nearest_vertex_to_lon_lat(double precision, double precision) does not exist

and also ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR: function get_nearest_vertex_to_lon_lat(double precision, double precision) does not exist

The weird part is that I execute this query on postgres and it works.

Answer 1

As it turns out, despite my config database.yml for production, I was unaware that Heroku creates its own production database, so obviously didn't know any custom sql function, or even tables.

For future reference, if you're deploying to heroku and you're using a remote database you have to do this first and then set your own DATABASE_URL