I have below code as part of oracle procedure ,Need help in converting into PostgreSQL procedure-
-- logic to send e-mail to multiple To'd users separated by ';'
v_temp := replace(p_recepient,' ','');
IF ( instr(v_temp,';') = 0) OR (instr(v_temp,';') < length(v_temp))
THEN
v_temp := v_temp || ';';
END IF;
v_pos := 1;
WHILE (instr(v_temp,';',v_pos) > 0)
LOOP
v_name := substr(v_temp,v_pos,instr(substr(v_temp,v_pos),';') - 1);
v_pos := v_pos + instr(substr(v_temp,v_pos),';');
v_reply := utl_smtp.rcpt(conn,v_name);
END LOOP;
IF v_reply.code <> 250
THEN
utl_smtp.quit(conn);
RETURN;
END IF;
-- logic to send e-mail to multiple CC's users separated by ';'
v_temp1 := REPLACE(p_recpnt_cc,' ','');
IF (instr(v_temp1,';') = 0) OR (instr(v_temp1,';') < length(v_temp1))
THEN
v_temp1 := v_temp1 || ';';
END IF;
v_pos1 := 1;
WHILE (instr(v_temp1,';',v_pos1) > 0)
LOOP
v_name1 := substr(v_temp1,v_pos1/*1*/,instr(substr(v_temp1,v_pos1),';') - 1);
v_pos1 := v_pos1 + instr(substr(v_temp1,v_pos1),';');
v_reply1 := utl_smtp.rcpt(conn,v_name1);
END LOOP;
IF v_reply1.code <> 250
THEN
utl_smtp.quit(conn);
RETURN;
END IF;
As you see the above code has instr ,substr which are not there in PostgreSQL.
when I run above code as part of PostgreSQL procedure I am getting error as -
ERROR: syntax error at or near "IF"
LINE 94: IF ( instr(v_temp,';') = 0) OR (instr(v_temp,';') < le...
^
********** Error **********
ERROR: syntax error at or near "IF"
SQL state: 42601
Character: 4028
