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, Mar 07 '19 at 11:59
@Alasdair Yes. I get the same error. And when I set float into the function, I get the same error. — xomem, Mar 07 '19 at 12:13
See also https://stackoverflow.com/questions/3202763/psycopg2-callproc-and-sql-parameters and https://stackoverflow.com/questions/28409134/string-passed-into-cursor-callproc-becomes-unknown-psycopg2-python-2-7-postgr — Risadinha, Mar 07 '19 at 14:06

score 1 · Accepted Answer · answered Mar 07 '19 at 12:51

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 07 '19 at 12:51

Daniel Hepper

28,981
10
72
75

Why doesn't Django see the postgres function

1 Answers1