Re: Question on replace function

On Sun, 2016-09-25 at 10:29 +0200, Charles Clavadetscher wrote:
> Hello
>
> I am using PostgreSQL 9.4.7 on x86_64-unknown-linux-gnu, compiled by
> gcc 
> (Debian 4.7.2-5) 4.7.2, 64-bit
>
> I imported data from a MariaDB table into PostgreSQL and noticed
> that 
> the content of a field was not correct, but I was not able to change
> it. 
> The field is called vcard and is of datatye text.
>
> The structure of the table:
>
> roundcubemail=# \d contacts
>                                         Tabelle „public.contacts“
>     Spalte   |           Typ            | 
> Attribute
> ------------+--------------------------+-----------------------------
> -----------------------------------
>   contact_id | integer                  | not null Vorgabewert 
> nextval(('contacts_seq'::text)::regclass)
>   changed    | timestamp with time zone | not null Vorgabewert now()
>   del        | smallint                 | not null Vorgabewert 0
>   name       | character varying(128)   | not null Vorgabewert 
> ''::character varying
>   email      | text                     | not null Vorgabewert
> ''::text
>   firstname  | character varying(128)   | not null Vorgabewert 
> ''::character varying
>   surname    | character varying(128)   | not null Vorgabewert 
> ''::character varying
>   vcard      | text                     |
>   words      | text                     |
>   user_id    | integer                  | not null
>
> The content of vcard looks as follows (replaced real names with 
> placeholders):
>
> BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname 
> ;;;\r\\rFN:Firstname 
> Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD
>
> My target is to replace all occurences of '\r\\r' with E'\r\n' to
> comply 
> with RFC 6350.
>
> I tried using the function replace and I am faced with a strange 
> behaviour. If I use the function with a string as shown above I get
> the 
> expected result:
>
> elect replace('BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname 
> ;;;\r\\rFN:Firstname 
> Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD','\r\
> \r',E'\r\n') 
> ;
>
>                  replace
> ----------------------------------------
>   BEGIN:VCARD\r                         +
>   VERSION:3.0\r                         +
>   N:;Firstname Lastname ;;;\r           +
>   FN:Firstname Lastname\r               +
>   EMAIL;TYPE=INTERNET;TYPE=OTHER:email\r+
>   END:VCARD
> (1 row)
>
> However, if I use the function on the vcard field nothing is
> replaced:
>
> select replace(vcard,'\r\\r',E'\r\n') from contacts;
>
>  
> replace
> -------------------------------------------------------------------
> -------------------------------------------------------------------
> ----
>   BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname 
> ;;;\r\\rFN:Firstname 
> Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD
> (1 row)
>
> Does anybody have an idea what I am doing wrong?
> Thank you for your help.
>
> Charles
>
> -- 
> Swiss PostgreSQL Users Group
> c/o Charles Clavadetscher
> Treasurer
> Motorenstrasse 18
> CH – 8005 Zürich
>
> http://www.swisspug.org
>
> +-----------------------+
> >
> >   ____  ______  ___   |
> >  /    )/      \/   \  |
> > (     / __    _\    ) |
> >  \    (/ o)  ( o)   ) |
> >   \_  (_  )   \ ) _/  |
> >     \  /\_/    \)/    |
> >      \/ <//|  |\\>    |
> >           _|  |       |
> >           \|_/        |
> >                       |
> > PostgreSQL 1996-2016  |
> >  20 Years of Success  |
> >                       |
> +-----------------------+
>
>

Tested this on 9.6beta3 on a test database and it appears to work fine.

Inserted one row.

dinkumerp=> select * from contacts;
LOG:  duration: 0.571 ms  statement: select * from contacts;
 contact_id |            changed            | del | name | email |
firstname | s
urname
|                              vcard                              |
words
 | user_id 
------------+-------------------------------+-----+------+-------+-----
------+--
-------+---------------------------------------------------------------
--+------
-+---------
          1 | 2016-09-25 21:30:54.788442+10 |   0
|      |       |           |  
       | BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname
Lastname          +|      
 |        
            |                               |     |      |       |     
      |  
       |
;;;\r\\rFN:Firstname                                           +|      
 | 
            |                               |     |      |       |     
      |  
       |
Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD |      
 | 
(1 row)

Replace select.

dinkumerp=> select replace(vcard,'\r\\r',E'\r\n') from contacts;
LOG:  duration: 0.400 ms  statement: select
replace(vcard,'\r\\r',E'\r\n') from contacts;
                replace                 
----------------------------------------
 BEGIN:VCARD\r                         +
 VERSION:3.0\r                         +
 N:;Firstname Lastname                 +
 ;;;\r                                 +
 FN:Firstname                          +
 Lastname\r                            +
 EMAIL;TYPE=INTERNET;TYPE=OTHER:email\r+
 END:VCARD
(1 row)

HTH,
Rob