Thread: lfcr with plpgsql

lfcr with plpgsql

From

"Derrick Betts"

Date:

08 March 2005, 22:36:40

I am building a text email from data in a database table and then delivering it from the database.

My question is how do I represent a carriage return or line feed in a plpgsql function? I am concatenating the different parts together, but I need to separate out certain sections with carriage returns.

Any help would be appreciated.

Thanks,

Derrick

Re: lfcr with plpgsql

From

Michael Fuhr

Date:

09 March 2005, 00:30:03

On Tue, Mar 08, 2005 at 12:36:16PM -0700, Derrick Betts wrote:

> My question is how do I represent a carriage return or line feed
> in a plpgsql function?

See "String Constants" in the "SQL Syntax" chapter of the documentation.
Here's a link to the latest version:

http://www.postgresql.org/docs/8.0/interactive/sql-syntax.html#SQL-SYNTAX-STRINGS

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: lfcr with plpgsql

From

"Derrick Betts"

Date:

09 March 2005, 01:12:47

It doesn't seem to be working that way, however.  This is the code that
writes the string:

Body := \'Hello \'||agentnm||\',\n\';
  Body := Body || \'This is a reminder for you to make contact with
\'||fname||\' \'||lname||\'.\n\';
  Body := Body || \'The time to make contact was \'||remtime||\'\n\n\';
  Body := Body || \'Thanks,\n\';
  Body := Body || \'The reminder system\';

And this is the result in the email that I receive:

Hello Derrick Betts, This is a reminder for you to make contact with Tom
Jones. The time to make contact was 2005-03-09 13:25:00 Thanks, The reminder
system

Any thoughts?
Thanks,
Derrick


----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Derrick Betts" <derrick@grifflink.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Tuesday, March 08, 2005 2:29 PM
Subject: Re: [NOVICE] lfcr with plpgsql


> On Tue, Mar 08, 2005 at 12:36:16PM -0700, Derrick Betts wrote:
>
>> My question is how do I represent a carriage return or line feed
>> in a plpgsql function?
>
> See "String Constants" in the "SQL Syntax" chapter of the documentation.
> Here's a link to the latest version:
>
> http://www.postgresql.org/docs/8.0/interactive/sql-syntax.html#SQL-SYNTAX-STRINGS
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

Re: lfcr with plpgsql

From

Michael Fuhr

Date:

09 March 2005, 03:42:12

On Tue, Mar 08, 2005 at 03:12:23PM -0700, Derrick Betts wrote:

> It doesn't seem to be working that way, however.  This is the code that
> writes the string:
>
> Body := \'Hello \'||agentnm||\',\n\';
>  Body := Body || \'This is a reminder for you to make contact with
> \'||fname||\' \'||lname||\'.\n\';
>  Body := Body || \'The time to make contact was \'||remtime||\'\n\n\';
>  Body := Body || \'Thanks,\n\';
>  Body := Body || \'The reminder system\';
>
> And this is the result in the email that I receive:
>
> Hello Derrick Betts, This is a reminder for you to make contact with Tom
> Jones. The time to make contact was 2005-03-09 13:25:00 Thanks, The
> reminder system

How are you sending and reading the mail?  I pasted your code into
a PL/pgSQL function that calls a PL/Perl function that sends email,
and it worked in my mail reader (i.e., the received message had
line breaks where the original message had \n characters).

Could you post a simple but complete example?  Something we could
copy into an empty database that would duplicate your results.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: lfcr with plpgsql

From

"Derrick Betts"

Date:

09 March 2005, 05:07:19

This is the line that sends the mail in the function in question:

PERFORM pgmail(\'Reminders@EvergreenMortgageInc.com\',email_1,\'Reminder
Notice\',Body,\'text/html\');
where the email_1 variable is the TO: address.  The Body variable is as
explained below.

This is the pgmail function:

CREATE OR REPLACE FUNCTION pgmail(text, text, text, text, text)
  RETURNS int4 AS
'
set mailfrom $1
set mailto $2
set mailsubject $3
set mailmessage $4
set contenttype $5
set myHost "127.0.0.1"
set myPort 25
set mySock [socket $myHost $myPort]
set toemailaddress_start [string first "<" $mailto]
if {$toemailaddress_start != -1} {
 set toemailaddress_finish [string first ">" $mailto]
 set toemailaddress_start [expr $toemailaddress_start + 1]
 set toemailaddress_finish [expr $toemailaddress_finish - 1]
 set toemailaddress [string range $mailto $toemailaddress_start
$toemailaddress_finish]
} else {
 set toemailaddress $mailto
}
set fromemailaddress_start [string first "<" $mailfrom]
if {$fromemailaddress_start != -1} {
 set fromemailaddress_finish [string first ">" $mailfrom]
 set fromemailaddress_start [expr $fromemailaddress_start + 1]
 set fromemailaddress_finish [expr $fromemailaddress_finish - 1]
 set fromemailaddress [string range $mailfrom $fromemailaddress_start
$fromemailaddress_finish]
} else {
 set fromemailaddress $mailfrom
}
fileevent $mySock writable [list svcHandler $mySock]
fconfigure $mySock -buffering none
puts $mySock "HELO 127.0.0.1"
gets $mySock name
puts $mySock "mail from: $fromemailaddress"
gets $mySock name
puts $mySock "rcpt to: $toemailaddress"
gets $mySock name
puts $mySock "data"
gets $mySock name
puts $mySock "To: $mailto"
puts $mySock "From: $mailfrom"
puts $mySock "Content-Type: $contenttype"
puts $mySock "Subject: $mailsubject"
puts $mySock ""
puts $mySock "$mailmessage"
puts $mySock "."
gets $mySock name
close $mySock
return 1'
  LANGUAGE 'pltclu' VOLATILE;


Thanks,
Derrick


----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Derrick Betts" <derrick@grifflink.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Tuesday, March 08, 2005 5:42 PM
Subject: Re: [NOVICE] lfcr with plpgsql


> On Tue, Mar 08, 2005 at 03:12:23PM -0700, Derrick Betts wrote:
>
>> It doesn't seem to be working that way, however.  This is the code that
>> writes the string:
>>
>> Body := \'Hello \'||agentnm||\',\n\';
>>  Body := Body || \'This is a reminder for you to make contact with
>> \'||fname||\' \'||lname||\'.\n\';
>>  Body := Body || \'The time to make contact was \'||remtime||\'\n\n\';
>>  Body := Body || \'Thanks,\n\';
>>  Body := Body || \'The reminder system\';
>>
>> And this is the result in the email that I receive:
>>
>> Hello Derrick Betts, This is a reminder for you to make contact with Tom
>> Jones. The time to make contact was 2005-03-09 13:25:00 Thanks, The
>> reminder system
>
> How are you sending and reading the mail?  I pasted your code into
> a PL/pgSQL function that calls a PL/Perl function that sends email,
> and it worked in my mail reader (i.e., the received message had
> line breaks where the original message had \n characters).
>
> Could you post a simple but complete example?  Something we could
> copy into an empty database that would duplicate your results.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>

Re: lfcr with plpgsql

From

Michael Fuhr

Date:

09 March 2005, 05:33:58

On Tue, Mar 08, 2005 at 07:06:57PM -0700, Derrick Betts wrote:

> PERFORM pgmail(\'Reminders@EvergreenMortgageInc.com\',email_1,\'Reminder
> Notice\',Body,\'text/html\');

If the MIME type is text/html and if the mail reader understands
HTML, then it might be ignoring newlines when it formats the message
for display.  What happens if you use text/plain or if you put <br>
at the end of each line?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: lfcr with plpgsql

From

"Derrick Betts"

Date:

09 March 2005, 05:44:03

That was it.  Good eye I must say.

Thanks!
Derrick
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Derrick Betts" <derrick@grifflink.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Tuesday, March 08, 2005 7:33 PM
Subject: Re: [NOVICE] lfcr with plpgsql


> On Tue, Mar 08, 2005 at 07:06:57PM -0700, Derrick Betts wrote:
>
>> PERFORM pgmail(\'Reminders@EvergreenMortgageInc.com\',email_1,\'Reminder
>> Notice\',Body,\'text/html\');
>
> If the MIME type is text/html and if the mail reader understands
> HTML, then it might be ignoring newlines when it formats the message
> for display.  What happens if you use text/plain or if you put <br>
> at the end of each line?
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>
>

Re: lfcr with plpgsql

From

Sean Davis

Date:

09 March 2005, 14:07:43

On Mar 8, 2005, at 9:33 PM, Michael Fuhr wrote:

> On Tue, Mar 08, 2005 at 07:06:57PM -0700, Derrick Betts wrote:
>
>> PERFORM
>> pgmail(\'Reminders@EvergreenMortgageInc.com\',email_1,\'Reminder
>> Notice\',Body,\'text/html\');
>
> If the MIME type is text/html and if the mail reader understands
> HTML, then it might be ignoring newlines when it formats the message
> for display.  What happens if you use text/plain or if you put <br>
> at the end of each line?
>

Or use <pre>

Sean