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
- CREATE OR REPLACE PROCEDURE populate_data_to_table(tableName IN text,employeecolumn IN text, status OUT integer) AS
- Declare
- table_data RECORD;
- employeeCnt INTEGER;
- employeeinsert INTEGER;
- table_Name text;
- insertquery text;
- updatequery text;
- selectquery text;
- BEGIN
- RAISE NOTICE 'begin';
- RAISE NOTICE 'tableName % ',tableName;
- selectquery:='SELECT '|| employeecolumn ||' as employeeid, COUNT(*)
- FROM '|| tableName ||' c
- GROUP BY c.'||employeecolumn||'';
- RAISE NOTICE 'selectquery % ',selectquery;
- FOR table_data IN
- EXECUTE selectquery
- LOOP
- RAISE NOTICE 'loop data %',table_data.employeeid;
- RAISE NOTICE 'loop COUNT %',table_data.COUNT;
- select count(empid) into employeeCnt from temp_table_data where empid = table_data.employeeid;
- RAISE NOTICE 'data Count %', employeeCnt;
- if employeeCnt = 0 then
- RAISE NOTICE 'data not found';
- insertquery := 'INSERT INTO temp_table_data(empid, '||tableName ||',last_updated_date)
- values (1,1,sysdate)';
- RAISE NOTICE 'insertquery %',insertquery;
- EXECUTE insertquery; --not able to execute this getting syntax error
- else
- RAISE NOTICE 'data found';
- updatequery :='UPDATE temp_table_data set '|| tableName ||'= table_data.COUNT,
- last_updated_date =sysdate where empid = table_data.employeeid';
- RAISE NOTICE 'updatequery %',updatequery;
- EXECUTE updatequery;
- end if;
- END LOOP;
- COMMIT;
- status := 0;
- EXCEPTION
- WHEN others THEN
- status := -1;
- RAISE NOTICE 'SQLERRM %',SQLERRM;
- END
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