Calling a stored procedure in given schema

Using Django Using the ORM
1

I’m trying to call a stored procedure, as follows:

with connection.cursor() as cursor:
cursor.execute("CALL schema_name.procedure_name(%s);", (obj_id,))

But the error below occurs:
django.db.utils.ProgrammingError: procedure schema_name.procedure_name(integer) does not exist
LINE 1: CALL schema_name.procedure_name(1);
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.

This would be reasonable if the stored procedure didn’t exist in given schema, but that’s not true.
Is it possible to call stored procedures in a given schema?

I’ve failed to find an answer in documentation and web searches.

Edit: Typo

2

The data types are critical here. How do you have the parameter defined in your procedure definition?

(If your procedure is expecting a character type field, passing it an integer is not going to work.)

3

Hi @KenWhitesell,

For testing purposes I’ve created the same procedure it in the default schema and it works. The point is that it would be really convenient if I could call from this other one. I should note that the creation process of these procedures was made directly in the database. I’m using postgresql.

Edit to add: The calling process is as follows:

with connection.cursor() as cursor:
cursor.execute("CALL procedure_name(%s);", (obj_id,))

Edit to add:
Responding directly:
CREATE OR REPLACE PROCEDURE schema_name.procedure_name(IN object_id integer)

4

What you have written does work as written - the issue has nothing to do with using a schema reference in the call.

If it’s not a parameter issue, then the next thing to check is the permissions - does the account being used for that connection have the appropriate permissions to the procedure?

You can test this from within psql. Connect to the database using the credentials that Django is using, and try to run the call statement directly.