CODEWITHSUNDEEP
pythondjangopsycopg2
xomem
xomem

Reputation: 147

Why doesn't Django see the postgres function

There is such a function:

def getNearestNotes(request, longitude, latitude):
    if request.method == 'GET':
    c = connection.cursor()
    r = None
    try:
        c.callproc('GetAllNotes', (float(longitude), float(latitude)))
        r = c.fetchall()
    finally:
        c.close()
        return HttpResponse(str(r))
    else:
        return HttpResponse('needGetMethod')

It should call such a function in the postgresql database:

create function "GetAllNotes"(long numeric, lat numeric)
  returns TABLE
  (
    UserId   integer,
    UserName character varying,
    NoteName character varying,
    NoteLong double precision,
    NoteLat  double precision
  )
  language plpgsql
  as
  $$
  BEGIN
  RETURN query (SELECT Notes."UserId", Users."Name", Notes."Name", 
  Notes."Longitude", Notes."Latitude"
        FROM Notes
               INNER JOIN Users ON Notes."UserId" = Users."Id"
        WHERE (point(long, lat) <@> point(Notes."Longitude", 
  Notes."Latitude") <= 0.124274));
  END
  $$;
  alter function "GetAllNotes"(numeric, numeric) owner to postgres;

But when calling this function, django gives an error -

function getallnotes(numeric, numeric) does not exist LINE 1: SELECT * FROM GetAllNotes(28.0,23.0)
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

The base is connected.

But if I do this -

c.execute("SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema='public'") r = c.fetchone() - then the 'GetAllNotes' function will be listed

Upvotes: 0

Views: 268

Answers (1)

Daniel Hepper
Daniel Hepper

Reputation: 29967

I think you have an issue with case sensitivity of function names in PostgreSQL.

Try this:

c.callproc('"GetAllNotes"', (float(longitude), float(latitude)))

Upvotes: 1

Related Questions