Re: Send email from PostgreSQL, may I ?

["Taras Kopets" <tkopets@xxxxxxxxx>]

Hi!
 

Gerson Machado wrote:
> I need send email directly from PG with on function, where locate this ?

Alvaro Herrera wrote:
> > Typically this is bad idea.  Better save the email info on a table and
> > fire a NOTIFY.  An external daemon would be listening to that
> > notification, and send the email from the data in the table, which it
> > can subsequently delete or mark as used.

If you still want to send e-mails directly from PG, you can try to use my 
function based on pgmail which will allow you to send e-mail using mailservers 
which require authorization and analyze relpy from smtp to be sure your 
e-mail is sent (look in attachment). You will need to install pltclu first.

PS: Maybe anyone knows how to send a unicode characters in e-mail using Tcl?

Taras Kopets
CREATE OR REPLACE FUNCTION com_tcl_sendmail(text, text, text, text)
RETURNS int4 AS $BODY$
  set mailfrom $1
  set mailto $2
  set mailsubject $3
  set mailcontent $4
  set ServerHost "<YOUR MAIL SERVER HERE>" ;# MAIL SERVER
  set ServerPort 25 ; # SMTP MAIL PORT
  set ServerSocket [socket $ServerHost $ServerPort]
  set toemail_begin [string first "<" $mailto]
  if {$toemail_begin != -1} {
    set toemail_end [string first ">" $mailto]
    set toemail_begin [expr $toemail_begin + 1]
    set toemail_end [expr $toemail_end - 1]
    set toemailaddress [string range $mailto $toemail_begin $toemail_end]
  } else {
      set toemailaddress $mailto
  }
  
  set fromemail_begin [string first "<" $mailfrom]
  if {$fromemail_begin != -1} {
    set fromemail_end [string first ">" $mailfrom]
    set fromemail_begin [expr $fromemail_begin + 1]
    set fromemail_end [expr $fromemail_end - 1]
    set fromemailaddress [string range $mailfrom $fromemail_begin $fromemail_end]
  } else {
      set fromemailaddress $mailfrom
  }
  
  fileevent $ServerSocket writable [list svcHandler $ServerSocket]
  fconfigure $ServerSocket -buffering none
  
  gets $ServerSocket ReplyString
  if {[regexp {^220} $ReplyString] == 0} {
    puts $ServerSocket "quit"                   ;# Quit
    return 0
  }  
  
  puts $ServerSocket "HELO <ENTER YOUR DATABASESERVER HERE>"  ;# HELO command
  gets $ServerSocket ReplyString
  if {[regexp {^250} $ReplyString] == 0} {
    puts $ServerSocket "quit"                   ;# Quit
    return 0
  }  
  
  
  # USE THIS ONLY IF YOU NEED AUTHORIZATION ON MAILSERVER
  # ------------------------------------------------
  # puts $ServerSocket "AUTH LOGIN"   ;# mailserver requires authorization
  # gets $ServerSocket ReplyString
  # if {[regexp {^334} $ReplyString] == 0} {
  #   puts $ServerSocket "quit"                   ;# Quit
  #   return 0
  # }  
  #   
  # puts $ServerSocket dGtvcGV0cw==   ;# username encoded with base64: SELECT encode('mailuser', 'base64')
  # gets $ServerSocket ReplyString
  # if {[regexp {^334} $ReplyString] == 0} {
  #   puts $ServerSocket "quit"                   ;# Quit
  #   return 0
  # }  
  # 
  # puts $ServerSocket cGFzc3dvcmQ=  ;# user password encoded with base64: SELECT encode('password', 'base64')
  # gets $ServerSocket ReplyString
  # if {[regexp {^235} $ReplyString] == 0} {
  #   puts $ServerSocket "quit"                   ;# Quit
  #   return 0
  # }  
  
  puts $ServerSocket "MAIL FROM: $fromemailaddress"  ;# mail from
  gets $ServerSocket ReplyString
  if {[regexp {^250} $ReplyString] == 0} {
    puts $ServerSocket "quit"                   ;# Quit
    return 0
  }  
  
  puts $ServerSocket "rcpt to: $toemailaddress"      ;# mail to
  gets $ServerSocket ReplyString
  if {[regexp {^250} $ReplyString] == 0} {
    puts $ServerSocket "quit"                   ;# Quit
    return 0
  }  
  
  puts $ServerSocket "DATA"  ;# begin letter content
  gets $ServerSocket ReplyString
  if {[regexp {^354} $ReplyString] == 0} {
    puts $ServerSocket "quit"                   ;# Quit
    return 0
  }  
  
  puts $ServerSocket "To: $mailto"            ;# To:
  puts $ServerSocket "From: $mailfrom"        ;# From:
  puts $ServerSocket "Subject: $mailsubject"  ;# Subject:  
  puts $ServerSocket ""
  puts $ServerSocket "$mailcontent"           ;# Content of message
  puts $ServerSocket "."                      ;# End of e-mail
  gets $ServerSocket ReplyString
  if {[regexp {^250} $ReplyString] == 0} {  
    return 0
  }
  
  puts $ServerSocket "quit"                   ;# Quit
  #gets $ServerSocket ReplyString
  
  close $ServerSocket
  return 1
$BODY$ language 'pltclu';