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

Not able to execute insert query using EXECUTE command in procedure

P: 1
Hi

I am not able to compile the procedure in postgresql. I am getting syntax error when compiling the below PROCEDURE
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE populate_data_to_table(tableName IN text,employeecolumn IN text, status OUT integer) AS 
  2.  
  3. Declare 
  4. table_data RECORD;
  5. employeeCnt INTEGER;
  6. employeeinsert INTEGER;
  7. table_Name text;
  8. insertquery text;
  9. updatequery text;
  10. selectquery text;
  11.  
  12. BEGIN
  13. RAISE NOTICE 'begin';
  14.  
  15. RAISE NOTICE 'tableName % ',tableName;
  16.  
  17. selectquery:='SELECT '|| employeecolumn ||' as employeeid, COUNT(*)
  18.   FROM '|| tableName ||' c
  19.   GROUP BY c.'||employeecolumn||'';
  20.   RAISE NOTICE 'selectquery % ',selectquery;
  21.  
  22. FOR table_data IN 
  23.  
  24. EXECUTE selectquery 
  25.  
  26. LOOP 
  27. RAISE NOTICE 'loop data %',table_data.employeeid;
  28. RAISE NOTICE 'loop COUNT %',table_data.COUNT;
  29.   select count(empid) into employeeCnt from temp_table_data where empid = table_data.employeeid;
  30. RAISE NOTICE 'data Count %', employeeCnt;
  31.  
  32. if employeeCnt = 0 then
  33. RAISE NOTICE 'data not found';
  34. insertquery := 'INSERT INTO temp_table_data(empid, '||tableName ||',last_updated_date) 
  35. values (1,1,sysdate)';
  36. RAISE NOTICE 'insertquery %',insertquery;
  37.  
  38. EXECUTE insertquery; --not able to execute this getting syntax error 
  39.  
  40. else
  41. RAISE NOTICE 'data found';
  42. updatequery :='UPDATE temp_table_data set '|| tableName ||'= table_data.COUNT,
  43. last_updated_date =sysdate where empid = table_data.employeeid';
  44. RAISE NOTICE 'updatequery %',updatequery;
  45. EXECUTE updatequery;
  46.  
  47. end if;
  48.  
  49. END LOOP;
  50.  
  51. COMMIT; 
  52.  
  53. status := 0;
  54.  
  55. EXCEPTION
  56.  
  57. WHEN others THEN 
  58.  
  59. status := -1; 
  60. RAISE NOTICE 'SQLERRM %',SQLERRM;
  61.  
  62. END
  63.  
ERROR: syntax error at or near "insertquery"
LINE 38: EXECUTE insertquery;
^
********** Error **********

ERROR: syntax error at or near "insertquery"
SQL state: 42601
Character: 1012

Can anyone tell me where I am doing wrong ?
Please help me to resolve this.

Thanks in advance.
Ravi
May 14 '14 #1
Share this question for a faster answer!
Share on Google+

Post your reply

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