CODEWITHSUNDEEP
postgresql
smile
smile

Reputation: 102

Syntax error while creating function in postgresql

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

Upvotes: 0

Views: 572

Answers (2)

smile
smile

Reputation: 102

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;

Upvotes: 0

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31193

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.

Upvotes: 1

Related Questions