I am working on a Django Project with a Postgres SQL Database. I have written a stored procedure that runs perfectly on Postgres.
Now I want to call that stored procedure from Django 1.5 .. I have written the code but it prompts errors.
CREATE FUNCTION fn_save_message3(IN msg_sub character varying, IN msg_cont text, IN msg_type character varying, IN msg_category character varying, IN msg_created_by character varying, IN msg_updated_by character varying) RETURNS integer AS
$BODY$ DECLARE msg_id integer := 0;
BEGIN
INSERT INTO tbl_messages
(message_subject, message_content, message_type, message_category,
created_on, created_by, updated_on, updated_by)
VALUES
(msg_sub, msg_cont, msg_type, msg_category, LOCALTIMESTAMP,
msg_created_by, LOCALTIMESTAMP, msg_updated_by);
Select into msg_id currval('tbl_messages_message_id_seq');
return msg_id;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100;
ALTER FUNCTION public.fn_save_message(IN character varying, IN text, IN character varying, IN character varying, IN character varying, IN character varying)
OWNER TO gljsxdlvpgfvui;
The Stored Procedure is Ok and it returns results.
c = connection.cursor()
try:
c.execute("BEGIN")
c.callproc("fn_save_message", [Message_Subject, Message_Content, Message_Type, Message_Category, Created_By, updated_by])
results = c.fetchone()
c.execute("COMMIT")
finally:
c.close()
print results
After all your suggestion my program is finally working. but one small issue left.
As i have used results = c.fetchone()
to fetch the out parameters.
it returns (13,)
but I only want to get 13 as a string or integer, how can I get only the value.
Updated:
the problem is solved using this
for item in results:
message_id = item
c = connection.cursor()
try:
c.execute("BEGIN")
c.callproc("fn_save_message3", (Message_Subject, Message_Content, Message_Type, Message_Category, Created_By, Updated_By))
results = c.fetchall()
c.execute("COMMIT")
finally:
c.close()
print results
You forgot the closing parens and were trying to call the functions on cursor
instead of c
and also had an issue with indentation. You should also use the callproc()
function as documented here.
As catavaran said, you should read the documentation on executing custom SQL and use placeholders. Also, in Django 1.6+, the transactions are commited automatically so there is no need for c.execute("COMMIT")
c = connection.cursor()
try:
c.execute("BEGIN")
c.callproc("fn_save_message3", [Message_Subject, Message_Content, Message_Type, Message_Category, Created_By, updated_by])
results = c.fetchone()
c.execute("COMMIT")
finally:
c.close()
for item in results:
message_id = item
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With