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';---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
