UTL_SMTP in postgresql

Question

Im curruntly migrating oracle schema to postgresql 9.5 . Im using Ora2pg and it converted for me one function which is reponsible for sending mail to pgplsql. My code :

CREATE OR REPLACE FUNCTION Control_Reports_Pg.send_error_mail (P_Str text, 
P_Function_Name text ) RETURNS VOID AS $body$
DECLARE


 V_Mail_Sender                      varchar(100) :=     
 '<BezeqInternational@bezeqint.co.il>';
 V_Mail_Recipients                  varchar(100) := 
 '<marinar@gmail.com>';
 V_Mail_Subject                     varchar(250):='Error in 
 '||C_Package_Name||'.'||P_Function_Name;
 V_Conn                             UTL_SMTP.CONNECTION;
 BEGIN

 V_Conn := Sa_Mail_Api_Pg.Begin_Mail(
          Sender     => V_Mail_Sender,
          Recipients => V_Mail_Recipients,
          Subject=>V_Mail_Subject,
          Mime_Type  => 'text/html; charset=windows-1255');

Sa_Mail_Api_Pg.Write_Mb_Text(
Conn    => V_Conn,
Message =>  P_Str);

Sa_Mail_Api_Pg.End_Mail( Conn => V_Conn );

PERFORM  
UTL_FILE.PUT_LINE(current_setting('Control_Reports_Pg.G_Log_File_Type');
 END;
  $body$
  LANGUAGE PLPGSQL
  SECURITY DEFINER
  ;

But i'm getting the next error :

ERROR:  schema "utl_smtp" does not exist
LINE 28:      V_Conn                             UTL_SMTP.CONNECTION;

Is there any UTL_SMTP package / schema that i can import ? What changes i need to do in order to send mail via postgresql ?

[pgsmtp](https://pgxn.org/dist/pgsmtp/) for instance. PostgreSQL is a DBMS, not e-mail client, but it can be expanded infinitely using various [procedural languages](https://www.postgresql.org/docs/current/static/xplang.html). But read [this](https://stackoverflow.com/a/12003516/593144) before you starts. — Abelisto, Jul 05 '17 at 16:49

score 0 · Answer 1 · answered Jul 14 '17 at 14:15

0

check the extension pgsmtp https://pgxn.org/dist/pgsmtp/0.1.1/, i wrote that to send mail from postgresql in a Oracle migration

answered Jul 14 '17 at 14:15

Anthony Sotolongo

1,395
2
9
17

UTL_SMTP in postgresql

1 Answers1