Syntax error while creating function in postgresql

Question

I got a syntax error while creating a procedure in postgresql.Here I attached my code.I got a error syntax error near "Continue"

create function patient_form_values() RETURNS void AS
 $$ begin
DECLARE columnName varchar(200) ;
DECLARE done boolean default true;
DECLARE CONTINUE handler for not found set done = false;
DECLARE cur1 cursor for select distinct COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'currentdiagnosis';
open cur1;
read_loop : loop
fetch from cur1 into columnName;
if done then leave read_loop; 
end if;
set @insertValues := concat('INSERT INTO patient_form_temp(patient_id, form_template_id, creator_id, created_date)
SELECT c.patient_id as patient_id, 41 AS form_template_id, 2 AS creator_id, c.created_date AS created_date 
FROM currentdiagnosis c 
WHERE c.', columnName,' IS NOT NULL GROUP BY c.patient_id, c.created_date'); 
select @insertValues;
prepare stmt from @insertValues;
execute stmt;
end loop;
close cur1;
end ;
$$ LANGUAGE plpgsql

Answer 1

You are trying to use a MySQL (or other DB?) function in PostgreSQL. There is no concept of CONTINUE HANDLER in PostgreSQL, so you have to convert the function into PostgreSQL format.

• Frank Heikens: You're right, this function has many syntax errors and won't work using the language plpgsql.
• smile: K Thanks for your suggestion.Can you say is there any option to change that
• Sami Kuhmonen: @smile The best option is to determine what the function actually does and rewrite it in pl/pgsql. There is no way to easily transform functions from other DB engines really.

Answer 2

drop FUNCTION if exists migratePartnerAdvertiser();
CREATE OR REPLACE FUNCTION migratePartnerAdvertiser() RETURNS int4 AS '
DECLARE r RECORD;
BEGIN
 FOR r IN select distinct COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''currentdiagnosis'' and table_schema=''public'' LOOP
 EXECUTE concat(''INSERT INTO patient_form_temp(patient_id, form_template_id, creator_id, created_date) SELECT c.patient_id as patient_id, 41 AS form_template_id, 2 AS creator_id, c.reg_date AS created_date FROM currentdiagnosis c WHERE c.'' , r.column_name , '' IS NOT NULL GROUP BY c.patient_id, c.reg_date'');
 END LOOP;
return 1;
END;
' LANGUAGE plpgsql;

• user330315: No need to drop the function if you are using create or replace