By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,663 Members | 762 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,663 IT Pros & Developers. It's quick & easy.

doubt in update query inside a function

priyan
P: 54
hi all,
I wrote a function in postgre to insert or update a row in to a table. In my function insert query is working fine but updation is not working please help me....

here is my function

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION savequestionbank_quest(pquestionid integer, pquestionbankid integer, pquestion text, pquestionsno integer, pquestionhistory text, pquestionparameter text, pquestionsource text, pquestionbibliograph text)
  2. RETURNS integer AS
  3. $BODY$
  4. Declare questionid integer;
  5. Begin
  6.     if pquestionid=0 then
  7.         insert into questionbank_quest(questionbankid,question,questsno,questhistory,questparamet,questsource,questbibliograp)
  8.              values(pquestionbankid,pquestion,pquestionsno,pquestionhistory,pquestionparameter,pquestionsource,pquestionbibliograph);
  9.         questionid := lastval();
  10.     else 
  11.         update questionbank_quest set questionbankid=pquestionbankid,question=pquestion,questsno=pquestionsno,questhistory=pquestionhistory,
  12.             questparamet=pquestionparameter,questsource=pquestionsource,questbibliograp=pquestionbibliograph where questionid=pquestionid;
  13.          questionid = pquestionid;
  14.     end if;
  15. return questionid;
  16. End
  17. $BODY$
  18. LANGUAGE 'plpgsql' VOLATILE;
  19.  
Oct 18 '07 #1
Share this Question
Share on Google+
2 Replies


priyan
P: 54
hi all,
I wrote a function in postgre to insert or update a row in to a table. In my function insert query is working fine but updation is not working please help me....

here is my function

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION savequestionbank_quest(pquestionid integer, pquestionbankid integer, pquestion text, pquestionsno integer, pquestionhistory text, pquestionparameter text, pquestionsource text, pquestionbibliograph text)
  2. RETURNS integer AS
  3. $BODY$
  4. Declare questionid integer;
  5. Begin
  6.     if pquestionid=0 then
  7.         insert into questionbank_quest(questionbankid,question,questsno,questhistory,questparamet,questsource,questbibliograp)
  8.              values(pquestionbankid,pquestion,pquestionsno,pquestionhistory,pquestionparameter,pquestionsource,pquestionbibliograph);
  9.         questionid := lastval();
  10.     else 
  11.         update questionbank_quest set questionbankid=pquestionbankid,question=pquestion,questsno=pquestionsno,questhistory=pquestionhistory,
  12.             questparamet=pquestionparameter,questsource=pquestionsource,questbibliograp=pquestionbibliograph where questionid=pquestionid;
  13.          questionid = pquestionid;
  14.     end if;
  15. return questionid;
  16. End
  17. $BODY$
  18. LANGUAGE 'plpgsql' VOLATILE;
  19.  


Please help me to solve this problem function is running properly and also insertion is taking place but updation alone is not working. Table is not get updated i don't no why it is like this.............please help me to solve this problem...........As soon as possible..............
Oct 18 '07 #2

amitpatel66
Expert 100+
P: 2,367
Please help me to solve this problem function is running properly and also insertion is taking place but updation alone is not working. Table is not get updated i don't no why it is like this.............please help me to solve this problem...........As soon as possible..............
Can you make sure that the data for the pquestionid that is passed as input parameter exists in the table??

and use COMMIT with in the procedure after INSERT and UPDATE statement in order to COMMIT the changes that are made!!
Oct 22 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.