Why doesn't Django see the postgres function

Question

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

Could there be a mismatch between numeric and the floats that you are passing in? Check whether you get the same error with decimals, e.g. from decimal import Decimal; c.callproc('GetAllNotes', (Decimal('51.5'), Decimal('0.0'))) — Alasdair
– Alasdair, Commented Mar 7, 2019 at 11:59
@Alasdair Yes. I get the same error. And when I set float into the function, I get the same error. — xomem
– xomem, Commented Mar 7, 2019 at 12:13
See also stackoverflow.com/questions/3202763/… and stackoverflow.com/questions/28409134/… — Risadinha
– Risadinha, Commented Mar 7, 2019 at 14:06

Daniel Hepper · Accepted Answer · 2019-03-07 12:51:32Z

1

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

Try this:

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

answered Mar 7, 2019 at 12:51

Daniel Hepper

30.1k10 gold badges78 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why doesn't Django see the postgres function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related