Home > mailing lists
Re: Escape handling in strings - Mailing list pgsql-patches

From	Christopher Kings-Lynne
Subject	Re: Escape handling in strings
Date	June 15, 2005 23:41:55
Msg-id	42B0E6DF.2010209@familyhealth.com.au Whole thread Raw
In response to	Escape handling in strings (Bruce Momjian <pgman@candle.pha.pa.us>)
Responses	Re: Escape handling in strings
List	pgsql-patches
Tree view
I'm still really iffy about this.  I think it will really hurt pgsql due
to backward compatibility :(

(If I'm understanding how the proposed change works...)

Chris


Bruce Momjian wrote:
> A summary of my proposal to add a new E'' string for escape and have
> non-E escapes not handle backslashes specially is at:
>
>     http://candle.pha.pa.us/cgi-bin/pgescape
>
> Attached is a patch that emits warnings for \ and \', perhaps for 8.1.
> The change to scan.l is the place this is done.  The rest of the patch
> is adjustments to prevent our own code from generating warnings.  It
> shows a good example of how users would have to change their code.
>
> It passes all regression tests, contrib regression, and initdb runs
> without warning.
>
>
>
> ------------------------------------------------------------------------
>
> Index: contrib/tsearch2/expected/tsearch2.out
> ===================================================================
> RCS file: /cvsroot/pgsql/contrib/tsearch2/expected/tsearch2.out,v
> retrieving revision 1.11
> diff -c -c -r1.11 tsearch2.out
> *** contrib/tsearch2/expected/tsearch2.out    14 Sep 2004 03:58:54 -0000    1.11
> --- contrib/tsearch2/expected/tsearch2.out    16 Jun 2005 01:36:54 -0000
> ***************
> *** 47,83 ****
>    '1' '2'
>   (1 row)
>
> ! SELECT '\'1 2\''::tsvector;
>    tsvector
>   ----------
>    '1 2'
>   (1 row)
>
> ! SELECT '\'1 \\\'2\''::tsvector;
>    tsvector
>   ----------
>    '1 \'2'
>   (1 row)
>
> ! SELECT '\'1 \\\'2\'3'::tsvector;
>     tsvector
>   -------------
>    '3' '1 \'2'
>   (1 row)
>
> ! SELECT '\'1 \\\'2\' 3'::tsvector;
>     tsvector
>   -------------
>    '3' '1 \'2'
>   (1 row)
>
> ! SELECT '\'1 \\\'2\' \' 3\' 4 '::tsvector;
>        tsvector
>   ------------------
>    '4' ' 3' '1 \'2'
>   (1 row)
>
> ! select '\'w\':4A,3B,2C,1D,5 a:8';
>          ?column?
>   -----------------------
>    'w':4A,3B,2C,1D,5 a:8
> --- 47,83 ----
>    '1' '2'
>   (1 row)
>
> ! SELECT '''1 2'''::tsvector;
>    tsvector
>   ----------
>    '1 2'
>   (1 row)
>
> ! SELECT E'''1 \\''2'''::tsvector;
>    tsvector
>   ----------
>    '1 \'2'
>   (1 row)
>
> ! SELECT E'''1 \\''2''3'::tsvector;
>     tsvector
>   -------------
>    '3' '1 \'2'
>   (1 row)
>
> ! SELECT E'''1 \\''2'' 3'::tsvector;
>     tsvector
>   -------------
>    '3' '1 \'2'
>   (1 row)
>
> ! SELECT E'''1 \\''2'' '' 3'' 4 '::tsvector;
>        tsvector
>   ------------------
>    '4' ' 3' '1 \'2'
>   (1 row)
>
> ! select '''w'':4A,3B,2C,1D,5 a:8';
>          ?column?
>   -----------------------
>    'w':4A,3B,2C,1D,5 a:8
> ***************
> *** 126,138 ****
>    '1'
>   (1 row)
>
> ! SELECT '\'1 2\''::tsquery;
>    tsquery
>   ---------
>    '1 2'
>   (1 row)
>
> ! SELECT '\'1 \\\'2\''::tsquery;
>    tsquery
>   ---------
>    '1 \'2'
> --- 126,138 ----
>    '1'
>   (1 row)
>
> ! SELECT '''1 2'''::tsquery;
>    tsquery
>   ---------
>    '1 2'
>   (1 row)
>
> ! SELECT E'''1 \\''2'''::tsquery;
>    tsquery
>   ---------
>    '1 \'2'
> ***************
> *** 330,342 ****
>    '1' & '2' & '4' & ( '5' | !'6' )
>   (1 row)
>
> ! SELECT '1&(\'2\'&(\' 4\'&(\\|5 | \'6 \\\' !|&\')))'::tsquery;
>                    tsquery
>   ------------------------------------------
>    '1' & '2' & ' 4' & ( '|5' | '6 \' !|&' )
>   (1 row)
>
> ! SELECT '\'the wether\':dc & \' sKies \':BC & a:d b:a';
>                    ?column?
>   ------------------------------------------
>    'the wether':dc & ' sKies ':BC & a:d b:a
> --- 330,342 ----
>    '1' & '2' & '4' & ( '5' | !'6' )
>   (1 row)
>
> ! SELECT E'1&(''2''&('' 4''&(\\|5 | ''6 \\'' !|&'')))'::tsquery;
>                    tsquery
>   ------------------------------------------
>    '1' & '2' & ' 4' & ( '|5' | '6 \' !|&' )
>   (1 row)
>
> ! SELECT '''the wether'':dc & '' sKies '':BC & a:d b:a';
>                    ?column?
>   ------------------------------------------
>    'the wether':dc & ' sKies ':BC & a:d b:a
> ***************
> *** 382,388 ****
>       23 | entity       | HTML Entity
>   (23 rows)
>
> ! select * from parse('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty');
>    tokid |                token
> --- 382,388 ----
>       23 | entity       | HTML Entity
>   (23 rows)
>
> ! select * from parse('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty');
>    tokid |                token
> ***************
> *** 529,535 ****
>        1 | qwerty
>   (138 rows)
>
> ! SELECT to_tsvector('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty');
>


                                                                        to_tsvector



                                
> --- 529,535 ----
>        1 | qwerty
>   (138 rows)
>
> ! SELECT to_tsvector('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty');
>


                                                                        to_tsvector



                                
> ***************
> *** 543,549 ****
>         2
>   (1 row)
>
> ! SELECT length(to_tsvector('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty'));
>    length
> --- 543,549 ----
>         2
>   (1 row)
>
> ! SELECT length(to_tsvector('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty'));
>    length
> ***************
> *** 563,569 ****
>    'qwe' & 'skies'
>   (1 row)
>
> ! select to_tsquery('default', '\'the wether\':dc & \'           sKies \':BC ');
>          to_tsquery
>   ------------------------
>    'wether':CD & 'sky':BC
> --- 563,569 ----
>    'qwe' & 'skies'
>   (1 row)
>
> ! select to_tsquery('default', '''the wether'':dc & ''           sKies '':BC ');
>          to_tsquery
>   ------------------------
>    'wether':CD & 'sky':BC
> ***************
> *** 729,735 ****
>   (1 row)
>
>   drop trigger tsvectorupdate on test_tsvector;
> ! create function wow(text) returns text as 'select $1 || \' copyright\'; ' language sql;
>   create trigger tsvectorupdate before update or insert on test_tsvector
>   for each row execute procedure tsearch2(a, wow, t);
>   insert into test_tsvector (t) values ('345 qwerty');
> --- 729,735 ----
>   (1 row)
>
>   drop trigger tsvectorupdate on test_tsvector;
> ! create function wow(text) returns text as 'select $1 || '' copyright''; ' language sql;
>   create trigger tsvectorupdate before update or insert on test_tsvector
>   for each row execute procedure tsearch2(a, wow, t);
>   insert into test_tsvector (t) values ('345 qwerty');
> Index: contrib/tsearch2/sql/tsearch2.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/contrib/tsearch2/sql/tsearch2.sql,v
> retrieving revision 1.7
> diff -c -c -r1.7 tsearch2.sql
> *** contrib/tsearch2/sql/tsearch2.sql    28 Jun 2004 16:18:56 -0000    1.7
> --- contrib/tsearch2/sql/tsearch2.sql    16 Jun 2005 01:36:54 -0000
> ***************
> *** 12,23 ****
>   SELECT ' 1'::tsvector;
>   SELECT ' 1 '::tsvector;
>   SELECT '1 2'::tsvector;
> ! SELECT '\'1 2\''::tsvector;
> ! SELECT '\'1 \\\'2\''::tsvector;
> ! SELECT '\'1 \\\'2\'3'::tsvector;
> ! SELECT '\'1 \\\'2\' 3'::tsvector;
> ! SELECT '\'1 \\\'2\' \' 3\' 4 '::tsvector;
> ! select '\'w\':4A,3B,2C,1D,5 a:8';
>   select 'a:3A b:2a'::tsvector || 'ba:1234 a:1B';
>   select setweight('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd zxc:81,567,222A'::tsvector, 'c');
>   select strip('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd'::tsvector);
> --- 12,23 ----
>   SELECT ' 1'::tsvector;
>   SELECT ' 1 '::tsvector;
>   SELECT '1 2'::tsvector;
> ! SELECT '''1 2'''::tsvector;
> ! SELECT E'''1 \\''2'''::tsvector;
> ! SELECT E'''1 \\''2''3'::tsvector;
> ! SELECT E'''1 \\''2'' 3'::tsvector;
> ! SELECT E'''1 \\''2'' '' 3'' 4 '::tsvector;
> ! select '''w'':4A,3B,2C,1D,5 a:8';
>   select 'a:3A b:2a'::tsvector || 'ba:1234 a:1B';
>   select setweight('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd zxc:81,567,222A'::tsvector, 'c');
>   select strip('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd'::tsvector);
> ***************
> *** 28,35 ****
>   SELECT '1 '::tsquery;
>   SELECT ' 1'::tsquery;
>   SELECT ' 1 '::tsquery;
> ! SELECT '\'1 2\''::tsquery;
> ! SELECT '\'1 \\\'2\''::tsquery;
>   SELECT '!1'::tsquery;
>   SELECT '1|2'::tsquery;
>   SELECT '1|!2'::tsquery;
> --- 28,35 ----
>   SELECT '1 '::tsquery;
>   SELECT ' 1'::tsquery;
>   SELECT ' 1 '::tsquery;
> ! SELECT '''1 2'''::tsquery;
> ! SELECT E'''1 \\''2'''::tsquery;
>   SELECT '!1'::tsquery;
>   SELECT '1|2'::tsquery;
>   SELECT '1|!2'::tsquery;
> ***************
> *** 62,92 ****
>   SELECT '1&2&4&5&6'::tsquery;
>   SELECT '1&(2&(4&(5|6)))'::tsquery;
>   SELECT '1&(2&(4&(5|!6)))'::tsquery;
> ! SELECT '1&(\'2\'&(\' 4\'&(\\|5 | \'6 \\\' !|&\')))'::tsquery;
> ! SELECT '\'the wether\':dc & \' sKies \':BC & a:d b:a';
>
>   select lexize('simple', 'ASD56 hsdkf');
>   select lexize('en_stem', 'SKIES Problems identity');
>
>   select * from token_type('default');
> ! select * from parse('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty');
>
> ! SELECT to_tsvector('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty');
>
>   SELECT length(to_tsvector('default', '345 qw'));
>
> ! SELECT length(to_tsvector('default', '345 qwe@efd.r \' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty'));
>
>
>   select to_tsquery('default', 'qwe & sKies ');
>   select to_tsquery('simple', 'qwe & sKies ');
> ! select to_tsquery('default', '\'the wether\':dc & \'           sKies \':BC ');
>   select to_tsquery('default', 'asd&(and|fghj)');
>   select to_tsquery('default', '(asd&and)|fghj');
>   select to_tsquery('default', '(asd&!and)|fghj');
> --- 62,92 ----
>   SELECT '1&2&4&5&6'::tsquery;
>   SELECT '1&(2&(4&(5|6)))'::tsquery;
>   SELECT '1&(2&(4&(5|!6)))'::tsquery;
> ! SELECT E'1&(''2''&('' 4''&(\\|5 | ''6 \\'' !|&'')))'::tsquery;
> ! SELECT '''the wether'':dc & '' sKies '':BC & a:d b:a';
>
>   select lexize('simple', 'ASD56 hsdkf');
>   select lexize('en_stem', 'SKIES Problems identity');
>
>   select * from token_type('default');
> ! select * from parse('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty');
>
> ! SELECT to_tsvector('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty');
>
>   SELECT length(to_tsvector('default', '345 qw'));
>
> ! SELECT length(to_tsvector('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw
1aew.werc.ewr/?ad=qwe&dw2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/?
ad=qwe&dw6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005
teodor@stack.netqwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 
>   /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2,
readline-4.2readline-4.2. 234  
>   <i <b> wow  < jqw <> qwerty'));
>
>
>   select to_tsquery('default', 'qwe & sKies ');
>   select to_tsquery('simple', 'qwe & sKies ');
> ! select to_tsquery('default', '''the wether'':dc & ''           sKies '':BC ');
>   select to_tsquery('default', 'asd&(and|fghj)');
>   select to_tsquery('default', '(asd&and)|fghj');
>   select to_tsquery('default', '(asd&!and)|fghj');
> ***************
> *** 135,141 ****
>   SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
>
>   drop trigger tsvectorupdate on test_tsvector;
> ! create function wow(text) returns text as 'select $1 || \' copyright\'; ' language sql;
>   create trigger tsvectorupdate before update or insert on test_tsvector
>   for each row execute procedure tsearch2(a, wow, t);
>   insert into test_tsvector (t) values ('345 qwerty');
> --- 135,141 ----
>   SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
>
>   drop trigger tsvectorupdate on test_tsvector;
> ! create function wow(text) returns text as 'select $1 || '' copyright''; ' language sql;
>   create trigger tsvectorupdate before update or insert on test_tsvector
>   for each row execute procedure tsearch2(a, wow, t);
>   insert into test_tsvector (t) values ('345 qwerty');
> Index: src/backend/parser/scan.l
> ===================================================================
> RCS file: /cvsroot/pgsql/src/backend/parser/scan.l,v
> retrieving revision 1.125
> diff -c -c -r1.125 scan.l
> *** src/backend/parser/scan.l    15 Jun 2005 16:28:06 -0000    1.125
> --- src/backend/parser/scan.l    16 Jun 2005 01:36:55 -0000
> ***************
> *** 49,54 ****
> --- 49,55 ----
>
>   static int        xcdepth = 0;    /* depth of nesting in slash-star comments */
>   static char    *dolqstart;      /* current $foo$ quote start string */
> + static bool        warn_on_escape;
>
>   /*
>    * literalbuf is used to accumulate literal values when multiple rules
> ***************
> *** 64,69 ****
> --- 65,71 ----
>   static void addlit(char *ytext, int yleng);
>   static void addlitchar(unsigned char ychar);
>   static char *litbufdup(void);
> + static void check_escape_warning(void);
>
>   /*
>    * When we parse a token that requires multiple lexer rules to process,
> ***************
> *** 185,190 ****
> --- 187,196 ----
>   /* National character */
>   xnstart            [nN]{quote}
>
> + /* Quote string does not warn about escapes */
> + xestart            [eE]{quote}
> + xeinside        [^']*
> +
>   /* Extended quote
>    * xqdouble implements embedded quote, ''''
>    */
> ***************
> *** 410,415 ****
> --- 416,428 ----
>                   }
>
>   {xqstart}        {
> +                     warn_on_escape = true;
> +                     token_start = yytext;
> +                     BEGIN(xq);
> +                     startlit();
> +                 }
> + {xestart}        {
> +                     warn_on_escape = false;
>                       token_start = yytext;
>                       BEGIN(xq);
>                       startlit();
> ***************
> *** 428,441 ****
> --- 441,468 ----
>                       addlit(yytext, yyleng);
>                   }
>   <xq>{xqescape}  {
> +                     if (yytext[1] == '\'')
> +                     {
> +                         if (warn_on_escape)
> +                             ereport(WARNING,
> +                                 (errcode(ERRCODE_INVALID_USE_OF_ESCAPE_CHARACTER),
> +                                  errmsg("Invalid use of \' in a normal string"),
> +                                  errhint("Use '' to place quotes in strings, or use E-type strings.")));
> +                     }
> +                     else
> +                         check_escape_warning();
>                       addlitchar(unescape_single_char(yytext[1]));
>                   }
>   <xq>{xqoctesc}  {
>                       unsigned char c = strtoul(yytext+1, NULL, 8);
> +
> +                     check_escape_warning();
>                       addlitchar(c);
>                   }
>   <xq>{xqhexesc}  {
>                       unsigned char c = strtoul(yytext+2, NULL, 16);
> +
> +                     check_escape_warning();
>                       addlitchar(c);
>                   }
>   <xq>{quotecontinue} {
> ***************
> *** 810,812 ****
> --- 837,850 ----
>               return c;
>       }
>   }
> +
> + static void
> + check_escape_warning(void)
> + {
> +     if (warn_on_escape)
> +         ereport(WARNING,
> +             (errcode(ERRCODE_INVALID_USE_OF_ESCAPE_CHARACTER),
> +              errmsg("Invalid use of escapes in a normal string"),
> +              errhint("Use E-type strings for escapes, e.g. E'\\r\\n'.")));
> +     warn_on_escape = false;    /* warn only once per string */
> + }
> Index: src/bin/initdb/initdb.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/bin/initdb/initdb.c,v
> retrieving revision 1.83
> diff -c -c -r1.83 initdb.c
> *** src/bin/initdb/initdb.c    30 Apr 2005 08:08:51 -0000    1.83
> --- src/bin/initdb/initdb.c    16 Jun 2005 01:36:57 -0000
> ***************
> *** 1688,1694 ****
>       char      **priv_lines;
>       static char *privileges_setup[] = {
>           "UPDATE pg_class "
> !         "  SET relacl = '{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' "
>           "  WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;\n",
>           "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n",
>           "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n",
> --- 1688,1694 ----
>       char      **priv_lines;
>       static char *privileges_setup[] = {
>           "UPDATE pg_class "
> !         "  SET relacl = E'{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' "
>           "  WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;\n",
>           "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n",
>           "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n",
> ***************
> *** 1952,1959 ****
>
>       for (i = 0, j = 0; i < len; i++)
>       {
> !         if (src[i] == '\'' || src[i] == '\\')
>               result[j++] = '\\';
>           result[j++] = src[i];
>       }
>       result[j] = '\0';
> --- 1952,1961 ----
>
>       for (i = 0, j = 0; i < len; i++)
>       {
> !         if (src[i] == '\\')
>               result[j++] = '\\';
> +         if (src[i] == '\'')        /* ANSI standard, '' */
> +             result[j++] = '\'';
>           result[j++] = src[i];
>       }
>       result[j] = '\0';
> Index: src/bin/pg_dump/pg_dumpall.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
> retrieving revision 1.59
> diff -c -c -r1.59 pg_dumpall.c
> *** src/bin/pg_dump/pg_dumpall.c    18 Apr 2005 23:47:52 -0000    1.59
> --- src/bin/pg_dump/pg_dumpall.c    16 Jun 2005 01:36:57 -0000
> ***************
> *** 538,544 ****
>                        "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
>                          "spclocation, spcacl "
>                          "FROM pg_catalog.pg_tablespace "
> !                        "WHERE spcname NOT LIKE 'pg\\_%'");
>
>       if (PQntuples(res) > 0)
>           printf("--\n-- Tablespaces\n--\n\n");
> --- 538,544 ----
>                        "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
>                          "spclocation, spcacl "
>                          "FROM pg_catalog.pg_tablespace "
> !                        "WHERE spcname NOT LIKE E'pg\\_%'");
>
>       if (PQntuples(res) > 0)
>           printf("--\n-- Tablespaces\n--\n\n");
> Index: src/bin/psql/describe.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
> retrieving revision 1.117
> diff -c -c -r1.117 describe.c
> *** src/bin/psql/describe.c    14 Jun 2005 23:59:31 -0000    1.117
> --- src/bin/psql/describe.c    16 Jun 2005 01:36:58 -0000
> ***************
> *** 1766,1772 ****
>       appendPQExpBuffer(&buf,
>         "\nFROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
>                         "       ON n.nspowner=u.usesysid\n"
> !                  "WHERE    (n.nspname NOT LIKE 'pg\\\\_temp\\\\_%%' OR\n"
>          "         n.nspname = (pg_catalog.current_schemas(true))[1])\n");        /* temp schema is first */
>
>       processNamePattern(&buf, pattern, true, false,
> --- 1766,1772 ----
>       appendPQExpBuffer(&buf,
>         "\nFROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
>                         "       ON n.nspowner=u.usesysid\n"
> !                  "WHERE    (n.nspname NOT LIKE E'pg\\\\_temp\\\\_%%' OR\n"
>          "         n.nspname = (pg_catalog.current_schemas(true))[1])\n");        /* temp schema is first */
>
>       processNamePattern(&buf, pattern, true, false,
> Index: src/include/catalog/pg_proc.h
> ===================================================================
> RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
> retrieving revision 1.367
> diff -c -c -r1.367 pg_proc.h
> *** src/include/catalog/pg_proc.h    14 Jun 2005 21:04:41 -0000    1.367
> --- src/include/catalog/pg_proc.h    16 Jun 2005 01:37:03 -0000
> ***************
> *** 1461,1467 ****
>   DESCR("greater-than-or-equal");
>   DATA(insert OID = 1157 (  timestamptz_gt   PGNSP PGUID 12 f f t f i 2 16 "1184 1184" _null_ _null_ _null_
timestamp_gt- _null_ )); 
>   DESCR("greater-than");
> ! DATA(insert OID = 1158 (  to_timestamp       PGNSP PGUID 14 f f t f i 1 1184 "701" _null_    _null_ _null_ "select
(\'epoch\'::timestamptz+ $1 * \'1 second\'::interval)" - _null_ )); 
>   DESCR("convert UNIX epoch to timestamptz");
>   DATA(insert OID = 1159 (  timezone           PGNSP PGUID 12 f f t f i 2 1114 "25 1184" _null_ _null_ _null_
timestamptz_zone- _null_ )); 
>   DESCR("adjust timestamp to new time zone");
> --- 1461,1467 ----
>   DESCR("greater-than-or-equal");
>   DATA(insert OID = 1157 (  timestamptz_gt   PGNSP PGUID 12 f f t f i 2 16 "1184 1184" _null_ _null_ _null_
timestamp_gt- _null_ )); 
>   DESCR("greater-than");
> ! DATA(insert OID = 1158 (  to_timestamp       PGNSP PGUID 14 f f t f i 1 1184 "701" _null_    _null_ _null_ "select
(''epoch''::timestamptz+ $1 * ''1 second''::interval)" - _null_ )); 
>   DESCR("convert UNIX epoch to timestamptz");
>   DATA(insert OID = 1159 (  timezone           PGNSP PGUID 12 f f t f i 2 1114 "25 1184" _null_ _null_ _null_
timestamptz_zone- _null_ )); 
>   DESCR("adjust timestamp to new time zone");
> ***************
> *** 1541,1547 ****
>
>   DATA(insert OID = 1215 (  obj_description    PGNSP PGUID 14 f f t f s 2    25 "26 19" _null_ _null_ _null_
"selectdescription from pg_catalog.pg_description where objoid = $1 and classoid = (select oid from pg_catalog.pg_class
whererelname = $2 and relnamespace = PGNSP) and objsubid = 0" - _null_ )); 
>   DESCR("get description for object id and catalog name");
> ! DATA(insert OID = 1216 (  col_description    PGNSP PGUID 14 f f t f s 2    25 "26 23" _null_ _null_ _null_
"selectdescription from pg_catalog.pg_description where objoid = $1 and classoid = \'pg_catalog.pg_class\'::regclass
andobjsubid = $2" - _null_ )); 
>   DESCR("get description for table column");
>
>   DATA(insert OID = 1217 (  date_trunc       PGNSP PGUID 12 f f t f s 2 1184 "25 1184" _null_ _null_ _null_
timestamptz_trunc- _null_ )); 
> --- 1541,1547 ----
>
>   DATA(insert OID = 1215 (  obj_description    PGNSP PGUID 14 f f t f s 2    25 "26 19" _null_ _null_ _null_
"selectdescription from pg_catalog.pg_description where objoid = $1 and classoid = (select oid from pg_catalog.pg_class
whererelname = $2 and relnamespace = PGNSP) and objsubid = 0" - _null_ )); 
>   DESCR("get description for object id and catalog name");
> ! DATA(insert OID = 1216 (  col_description    PGNSP PGUID 14 f f t f s 2    25 "26 23" _null_ _null_ _null_
"selectdescription from pg_catalog.pg_description where objoid = $1 and classoid = ''pg_catalog.pg_class''::regclass
andobjsubid = $2" - _null_ )); 
>   DESCR("get description for table column");
>
>   DATA(insert OID = 1217 (  date_trunc       PGNSP PGUID 12 f f t f s 2 1184 "25 1184" _null_ _null_ _null_
timestamptz_trunc- _null_ )); 
> ***************
> *** 2185,2193 ****
>   DESCR("return portion of string");
>   DATA(insert OID =  878 (  translate    PGNSP PGUID 12 f f t f i 3 25 "25 25 25" _null_ _null_ _null_    translate -
_null_)); 
>   DESCR("map a set of character appearing in string");
> ! DATA(insert OID =  879 (  lpad           PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select
pg_catalog.lpad($1,$2, \' \')" - _null_ )); 
>   DESCR("left-pad string to length");
> ! DATA(insert OID =  880 (  rpad           PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select
pg_catalog.rpad($1,$2, \' \')" - _null_ )); 
>   DESCR("right-pad string to length");
>   DATA(insert OID =  881 (  ltrim           PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_  ltrim1 - _null_
));
>   DESCR("trim spaces from left end of string");
> --- 2185,2193 ----
>   DESCR("return portion of string");
>   DATA(insert OID =  878 (  translate    PGNSP PGUID 12 f f t f i 3 25 "25 25 25" _null_ _null_ _null_    translate -
_null_)); 
>   DESCR("map a set of character appearing in string");
> ! DATA(insert OID =  879 (  lpad           PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select
pg_catalog.lpad($1,$2, '' '')" - _null_ )); 
>   DESCR("left-pad string to length");
> ! DATA(insert OID =  880 (  rpad           PGNSP PGUID 14 f f t f i 2 25 "25 23" _null_ _null_ _null_ "select
pg_catalog.rpad($1,$2, '' '')" - _null_ )); 
>   DESCR("right-pad string to length");
>   DATA(insert OID =  881 (  ltrim           PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_  ltrim1 - _null_
));
>   DESCR("trim spaces from left end of string");
> Index: src/test/regress/expected/arrays.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/arrays.out,v
> retrieving revision 1.25
> diff -c -c -r1.25 arrays.out
> *** src/test/regress/expected/arrays.out    22 Apr 2005 21:58:32 -0000    1.25
> --- src/test/regress/expected/arrays.out    16 Jun 2005 01:37:04 -0000
> ***************
> *** 436,442 ****
>   ERROR:  malformed array literal: "{{1,{2}},{2,3}}"
>   select '{{},{}}'::text[];
>   ERROR:  malformed array literal: "{{},{}}"
> ! select '{{1,2},\\{2,3}}'::text[];
>   ERROR:  malformed array literal: "{{1,2},\{2,3}}"
>   select '{{"1 2" x},{3}}'::text[];
>   ERROR:  malformed array literal: "{{"1 2" x},{3}}"
> --- 436,442 ----
>   ERROR:  malformed array literal: "{{1,{2}},{2,3}}"
>   select '{{},{}}'::text[];
>   ERROR:  malformed array literal: "{{},{}}"
> ! select E'{{1,2},\\{2,3}}'::text[];
>   ERROR:  malformed array literal: "{{1,2},\{2,3}}"
>   select '{{"1 2" x},{3}}'::text[];
>   ERROR:  malformed array literal: "{{"1 2" x},{3}}"
> Index: src/test/regress/expected/copy2.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/copy2.out,v
> retrieving revision 1.21
> diff -c -c -r1.21 copy2.out
> *** src/test/regress/expected/copy2.out    13 May 2005 06:33:40 -0000    1.21
> --- src/test/regress/expected/copy2.out    16 Jun 2005 01:37:04 -0000
> ***************
> *** 49,55 ****
>   -- various COPY options: delimiters, oids, NULL string
>   COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
>   COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
> ! COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
>   -- check results of copy in
>   SELECT * FROM x;
>      a   | b  |     c      |   d    |          e
> --- 49,55 ----
>   -- various COPY options: delimiters, oids, NULL string
>   COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
>   COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
> ! COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X';
>   -- check results of copy in
>   SELECT * FROM x;
>      a   | b  |     c      |   d    |          e
> ***************
> *** 176,183 ****
>       col1 text,
>       col2 text
>   );
> ! INSERT INTO y VALUES ('Jackson, Sam', '\\h');
> ! INSERT INTO y VALUES ('It is "perfect".','\t');
>   INSERT INTO y VALUES ('', NULL);
>   COPY y TO stdout WITH CSV;
>   "Jackson, Sam",\h
> --- 176,183 ----
>       col1 text,
>       col2 text
>   );
> ! INSERT INTO y VALUES ('Jackson, Sam', E'\\h');
> ! INSERT INTO y VALUES ('It is "perfect".',E'\t');
>   INSERT INTO y VALUES ('', NULL);
>   COPY y TO stdout WITH CSV;
>   "Jackson, Sam",\h
> ***************
> *** 187,193 ****
>   Jackson, Sam|\h
>   It is "perfect".|
>   ''|
> ! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE '\\';
>   "Jackson, Sam","\\h"
>   "It is \"perfect\".","    "
>   "",
> --- 187,193 ----
>   Jackson, Sam|\h
>   It is "perfect".|
>   ''|
> ! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\';
>   "Jackson, Sam","\\h"
>   "It is \"perfect\".","    "
>   "",
> Index: src/test/regress/expected/int8.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/int8.out,v
> retrieving revision 1.9
> diff -c -c -r1.9 int8.out
> *** src/test/regress/expected/int8.out    4 Oct 2004 14:42:47 -0000    1.9
> --- src/test/regress/expected/int8.out    16 Jun 2005 01:37:04 -0000
> ***************
> *** 280,286 ****
>               |  -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
>   (5 rows)
>
> ! SELECT '' AS to_char_16, to_char(q2, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM
INT8_TBL;
>    to_char_16 |                          to_char
>   ------------+-----------------------------------------------------------
>               |       text      9999     "text between quote marks"   456
> --- 280,286 ----
>               |  -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
>   (5 rows)
>
> ! SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM
INT8_TBL;
>    to_char_16 |                          to_char
>   ------------+-----------------------------------------------------------
>               |       text      9999     "text between quote marks"   456
> Index: src/test/regress/expected/numeric.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/numeric.out,v
> retrieving revision 1.16
> diff -c -c -r1.16 numeric.out
> *** src/test/regress/expected/numeric.out    28 Oct 2004 18:55:07 -0000    1.16
> --- src/test/regress/expected/numeric.out    16 Jun 2005 01:37:05 -0000
> ***************
> *** 1072,1078 ****
>               |          -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2
>   (10 rows)
>
> ! SELECT '' AS to_char_20, to_char(val, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM
num_data;
>    to_char_20 |                          to_char
>   ------------+-----------------------------------------------------------
>               |       text      9999     "text between quote marks"     0
> --- 1072,1078 ----
>               |          -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2
>   (10 rows)
>
> ! SELECT '' AS to_char_20, to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM
num_data;
>    to_char_20 |                          to_char
>   ------------+-----------------------------------------------------------
>               |       text      9999     "text between quote marks"     0
> Index: src/test/regress/expected/rowtypes.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/rowtypes.out,v
> retrieving revision 1.2
> diff -c -c -r1.2 rowtypes.out
> *** src/test/regress/expected/rowtypes.out    9 Jun 2004 19:08:20 -0000    1.2
> --- src/test/regress/expected/rowtypes.out    16 Jun 2005 01:37:05 -0000
> ***************
> *** 25,31 ****
>    (Joe,"von Blow") | (Joe,d'Blow)
>   (1 row)
>
> ! select '(Joe,"von""Blow")'::fullname, '(Joe,d\\\\Blow)'::fullname;
>        fullname      |    fullname
>   -------------------+-----------------
>    (Joe,"von""Blow") | (Joe,"d\\Blow")
> --- 25,31 ----
>    (Joe,"von Blow") | (Joe,d'Blow)
>   (1 row)
>
> ! select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname;
>        fullname      |    fullname
>   -------------------+-----------------
>    (Joe,"von""Blow") | (Joe,"d\\Blow")
> Index: src/test/regress/expected/timestamp.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamp.out,v
> retrieving revision 1.27
> diff -c -c -r1.27 timestamp.out
> *** src/test/regress/expected/timestamp.out    3 Jun 2004 02:08:06 -0000    1.27
> --- src/test/regress/expected/timestamp.out    16 Jun 2005 01:37:06 -0000
> ***************
> *** 1044,1050 ****
>              | 05 05 17 32 01 63121
>   (64 rows)
>
> ! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
>      FROM TIMESTAMP_TBL;
>    to_char_6 |                     to_char
>   -----------+-------------------------------------------------
> --- 1044,1050 ----
>              | 05 05 17 32 01 63121
>   (64 rows)
>
> ! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
>      FROM TIMESTAMP_TBL;
>    to_char_6 |                     to_char
>   -----------+-------------------------------------------------
> ***************
> *** 1358,1364 ****
>   (1 row)
>
>   SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> !                                           'HH "\\text between quote marks\\"" YY MI SS');
>    to_timestamp_6 |         to_timestamp
>   ----------------+------------------------------
>                   | Thu Jan 01 15:54:45 1998 PST
> --- 1358,1364 ----
>   (1 row)
>
>   SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> !                                           E'HH "\\text between quote marks\\"" YY MI SS');
>    to_timestamp_6 |         to_timestamp
>   ----------------+------------------------------
>                   | Thu Jan 01 15:54:45 1998 PST
> Index: src/test/regress/expected/timestamptz.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamptz.out,v
> retrieving revision 1.17
> diff -c -c -r1.17 timestamptz.out
> *** src/test/regress/expected/timestamptz.out    11 Jul 2004 04:57:20 -0000    1.17
> --- src/test/regress/expected/timestamptz.out    16 Jun 2005 01:37:07 -0000
> ***************
> *** 1041,1047 ****
>              | 05 05 17 32 01 63121
>   (64 rows)
>
> ! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
>      FROM TIMESTAMPTZ_TBL;
>    to_char_6 |                     to_char
>   -----------+-------------------------------------------------
> --- 1041,1047 ----
>              | 05 05 17 32 01 63121
>   (64 rows)
>
> ! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
>      FROM TIMESTAMPTZ_TBL;
>    to_char_6 |                     to_char
>   -----------+-------------------------------------------------
> ***************
> *** 1427,1433 ****
>   (1 row)
>
>   SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> !                                           'HH "\\text between quote marks\\"" YY MI SS');
>    to_timestamp_6 |         to_timestamp
>   ----------------+------------------------------
>                   | Thu Jan 01 15:54:45 1998 PST
> --- 1427,1433 ----
>   (1 row)
>
>   SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> !                                           E'HH "\\text between quote marks\\"" YY MI SS');
>    to_timestamp_6 |         to_timestamp
>   ----------------+------------------------------
>                   | Thu Jan 01 15:54:45 1998 PST
> Index: src/test/regress/expected/type_sanity.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/type_sanity.out,v
> retrieving revision 1.25
> diff -c -c -r1.25 type_sanity.out
> *** src/test/regress/expected/type_sanity.out    30 Apr 2005 20:31:39 -0000    1.25
> --- src/test/regress/expected/type_sanity.out    16 Jun 2005 01:37:07 -0000
> ***************
> *** 59,65 ****
>   -- NOTE: as of 8.0, this check finds smgr and unknown.
>   SELECT p1.oid, p1.typname
>   FROM pg_type as p1
> ! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE '\\_%' AND NOT EXISTS
>       (SELECT 1 FROM pg_type as p2
>        WHERE p2.typname = ('_' || p1.typname)::name AND
>              p2.typelem = p1.oid);
> --- 59,65 ----
>   -- NOTE: as of 8.0, this check finds smgr and unknown.
>   SELECT p1.oid, p1.typname
>   FROM pg_type as p1
> ! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
>       (SELECT 1 FROM pg_type as p2
>        WHERE p2.typname = ('_' || p1.typname)::name AND
>              p2.typelem = p1.oid);
> Index: src/test/regress/input/copy.source
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/input/copy.source,v
> retrieving revision 1.12
> diff -c -c -r1.12 copy.source
> *** src/test/regress/input/copy.source    10 May 2005 00:16:07 -0000    1.12
> --- src/test/regress/input/copy.source    16 Jun 2005 01:37:07 -0000
> ***************
> *** 62,71 ****
>       test     text,
>       filler    int);
>
> ! insert into copytest values('DOS','abc\r\ndef',1);
> ! insert into copytest values('Unix','abc\ndef',2);
> ! insert into copytest values('Mac','abc\rdef',3);
> ! insert into copytest values('esc\\ape','a\\r\\\r\\\n\\nb',4);
>
>   copy copytest to '@abs_builddir@/results/copytest.csv' csv;
>
> --- 62,71 ----
>       test     text,
>       filler    int);
>
> ! insert into copytest values('DOS',E'abc\r\ndef',1);
> ! insert into copytest values('Unix',E'abc\ndef',2);
> ! insert into copytest values('Mac',E'abc\rdef',3);
> ! insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
>
>   copy copytest to '@abs_builddir@/results/copytest.csv' csv;
>
> ***************
> *** 79,87 ****
>
>   --- same test but with an escape char different from quote char
>
> ! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';
>
> ! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';
>
>   select * from copytest except select * from copytest2;
>
> --- 79,87 ----
>
>   --- same test but with an escape char different from quote char
>
> ! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
>
> ! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
>
>   select * from copytest except select * from copytest2;
>
> Index: src/test/regress/output/copy.source
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/output/copy.source,v
> retrieving revision 1.10
> diff -c -c -r1.10 copy.source
> *** src/test/regress/output/copy.source    10 May 2005 00:16:07 -0000    1.10
> --- src/test/regress/output/copy.source    16 Jun 2005 01:37:08 -0000
> ***************
> *** 37,46 ****
>       style    text,
>       test     text,
>       filler    int);
> ! insert into copytest values('DOS','abc\r\ndef',1);
> ! insert into copytest values('Unix','abc\ndef',2);
> ! insert into copytest values('Mac','abc\rdef',3);
> ! insert into copytest values('esc\\ape','a\\r\\\r\\\n\\nb',4);
>   copy copytest to '@abs_builddir@/results/copytest.csv' csv;
>   create temp table copytest2 (like copytest);
>   copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
> --- 37,46 ----
>       style    text,
>       test     text,
>       filler    int);
> ! insert into copytest values('DOS',E'abc\r\ndef',1);
> ! insert into copytest values('Unix',E'abc\ndef',2);
> ! insert into copytest values('Mac',E'abc\rdef',3);
> ! insert into copytest values(E'esc\\ape',E'a\\r\\\r\\\n\\nb',4);
>   copy copytest to '@abs_builddir@/results/copytest.csv' csv;
>   create temp table copytest2 (like copytest);
>   copy copytest2 from '@abs_builddir@/results/copytest.csv' csv;
> ***************
> *** 51,58 ****
>
>   truncate copytest2;
>   --- same test but with an escape char different from quote char
> ! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';
> ! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '\'' escape '\\';
>   select * from copytest except select * from copytest2;
>    style | test | filler
>   -------+------+--------
> --- 51,58 ----
>
>   truncate copytest2;
>   --- same test but with an escape char different from quote char
> ! copy copytest to '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
> ! copy copytest2 from '@abs_builddir@/results/copytest.csv' csv quote '''' escape E'\\';
>   select * from copytest except select * from copytest2;
>    style | test | filler
>   -------+------+--------
> Index: src/test/regress/sql/arrays.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/arrays.sql,v
> retrieving revision 1.20
> diff -c -c -r1.20 arrays.sql
> *** src/test/regress/sql/arrays.sql    22 Apr 2005 21:58:32 -0000    1.20
> --- src/test/regress/sql/arrays.sql    16 Jun 2005 01:37:08 -0000
> ***************
> *** 204,210 ****
>   -- none of the following should be accepted
>   select '{{1,{2}},{2,3}}'::text[];
>   select '{{},{}}'::text[];
> ! select '{{1,2},\\{2,3}}'::text[];
>   select '{{"1 2" x},{3}}'::text[];
>   select '{}}'::text[];
>   select '{ }}'::text[];
> --- 204,210 ----
>   -- none of the following should be accepted
>   select '{{1,{2}},{2,3}}'::text[];
>   select '{{},{}}'::text[];
> ! select E'{{1,2},\\{2,3}}'::text[];
>   select '{{"1 2" x},{3}}'::text[];
>   select '{}}'::text[];
>   select '{ }}'::text[];
> Index: src/test/regress/sql/copy2.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/copy2.sql,v
> retrieving revision 1.12
> diff -c -c -r1.12 copy2.sql
> *** src/test/regress/sql/copy2.sql    13 May 2005 06:33:40 -0000    1.12
> --- src/test/regress/sql/copy2.sql    16 Jun 2005 01:37:08 -0000
> ***************
> *** 83,89 ****
>   3000;;c;;
>   \.
>
> ! COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
>   4000:\X:C:\X:\X
>   4001:1:empty::
>   4002:2:null:\X:\X
> --- 83,89 ----
>   3000;;c;;
>   \.
>
> ! COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X';
>   4000:\X:C:\X:\X
>   4001:1:empty::
>   4002:2:null:\X:\X
> ***************
> *** 121,133 ****
>       col2 text
>   );
>
> ! INSERT INTO y VALUES ('Jackson, Sam', '\\h');
> ! INSERT INTO y VALUES ('It is "perfect".','\t');
>   INSERT INTO y VALUES ('', NULL);
>
>   COPY y TO stdout WITH CSV;
>   COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
> ! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE '\\';
>
>   --test that we read consecutive LFs properly
>
> --- 121,133 ----
>       col2 text
>   );
>
> ! INSERT INTO y VALUES ('Jackson, Sam', E'\\h');
> ! INSERT INTO y VALUES ('It is "perfect".',E'\t');
>   INSERT INTO y VALUES ('', NULL);
>
>   COPY y TO stdout WITH CSV;
>   COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
> ! COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\';
>
>   --test that we read consecutive LFs properly
>
> Index: src/test/regress/sql/int8.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/int8.sql,v
> retrieving revision 1.7
> diff -c -c -r1.7 int8.sql
> *** src/test/regress/sql/int8.sql    4 Oct 2004 14:42:48 -0000    1.7
> --- src/test/regress/sql/int8.sql    16 Jun 2005 01:37:08 -0000
> ***************
> *** 61,65 ****
>   SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000')  FROM INT8_TBL;
>   SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
>   SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL;
> ! SELECT '' AS to_char_16, to_char(q2, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM
INT8_TBL;
>   SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999')     FROM INT8_TBL;
> --- 61,65 ----
>   SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000')  FROM INT8_TBL;
>   SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
>   SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL;
> ! SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM
INT8_TBL;
>   SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999')     FROM INT8_TBL;
> Index: src/test/regress/sql/numeric.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/numeric.sql,v
> retrieving revision 1.11
> diff -c -c -r1.11 numeric.sql
> *** src/test/regress/sql/numeric.sql    28 Oct 2004 18:55:08 -0000    1.11
> --- src/test/regress/sql/numeric.sql    16 Jun 2005 01:37:08 -0000
> ***************
> *** 742,748 ****
>   SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999')    FROM num_data;
>   SELECT '' AS to_char_18, to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM
num_data;
>   SELECT '' AS to_char_19, to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9')
FROMnum_data; 
> ! SELECT '' AS to_char_20, to_char(val, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM
num_data;
>   SELECT '' AS to_char_21, to_char(val, '999999SG9999999999')            FROM num_data;
>   SELECT '' AS to_char_22, to_char(val, 'FM9999999999999999.999999999999999')    FROM num_data;
>
> --- 742,748 ----
>   SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999')    FROM num_data;
>   SELECT '' AS to_char_18, to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM
num_data;
>   SELECT '' AS to_char_19, to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9')
FROMnum_data; 
> ! SELECT '' AS to_char_20, to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM
num_data;
>   SELECT '' AS to_char_21, to_char(val, '999999SG9999999999')            FROM num_data;
>   SELECT '' AS to_char_22, to_char(val, 'FM9999999999999999.999999999999999')    FROM num_data;
>
> Index: src/test/regress/sql/rowtypes.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/rowtypes.sql,v
> retrieving revision 1.2
> diff -c -c -r1.2 rowtypes.sql
> *** src/test/regress/sql/rowtypes.sql    9 Jun 2004 19:08:20 -0000    1.2
> --- src/test/regress/sql/rowtypes.sql    16 Jun 2005 01:37:08 -0000
> ***************
> *** 20,26 ****
>
>   select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;
>
> ! select '(Joe,"von""Blow")'::fullname, '(Joe,d\\\\Blow)'::fullname;
>
>   select '(Joe,"Blow,Jr")'::fullname;
>
> --- 20,26 ----
>
>   select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;
>
> ! select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname;
>
>   select '(Joe,"Blow,Jr")'::fullname;
>
> Index: src/test/regress/sql/timestamp.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/timestamp.sql,v
> retrieving revision 1.13
> diff -c -c -r1.13 timestamp.sql
> *** src/test/regress/sql/timestamp.sql    5 Mar 2004 02:41:14 -0000    1.13
> --- src/test/regress/sql/timestamp.sql    16 Jun 2005 01:37:09 -0000
> ***************
> *** 186,192 ****
>   SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
>      FROM TIMESTAMP_TBL;
>
> ! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
>      FROM TIMESTAMP_TBL;
>
>   SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
> --- 186,192 ----
>   SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
>      FROM TIMESTAMP_TBL;
>
> ! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
>      FROM TIMESTAMP_TBL;
>
>   SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
> ***************
> *** 211,217 ****
>   SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
>
>   SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> !                                           'HH "\\text between quote marks\\"" YY MI SS');
>
>   SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');
>
> --- 211,217 ----
>   SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
>
>   SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> !                                           E'HH "\\text between quote marks\\"" YY MI SS');
>
>   SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');
>
> Index: src/test/regress/sql/timestamptz.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/timestamptz.sql,v
> retrieving revision 1.6
> diff -c -c -r1.6 timestamptz.sql
> *** src/test/regress/sql/timestamptz.sql    5 Mar 2004 02:41:14 -0000    1.6
> --- src/test/regress/sql/timestamptz.sql    16 Jun 2005 01:37:09 -0000
> ***************
> *** 179,185 ****
>   SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
>      FROM TIMESTAMPTZ_TBL;
>
> ! SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
>      FROM TIMESTAMPTZ_TBL;
>
>   SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
> --- 179,185 ----
>   SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
>      FROM TIMESTAMPTZ_TBL;
>
> ! SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
>      FROM TIMESTAMPTZ_TBL;
>
>   SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
> ***************
> *** 207,213 ****
>   SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
>
>   SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> !                                           'HH "\\text between quote marks\\"" YY MI SS');
>
>   SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');
>
> --- 207,213 ----
>   SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
>
>   SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
> !                                           E'HH "\\text between quote marks\\"" YY MI SS');
>
>   SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY');
>
> Index: src/test/regress/sql/type_sanity.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/type_sanity.sql,v
> retrieving revision 1.25
> diff -c -c -r1.25 type_sanity.sql
> *** src/test/regress/sql/type_sanity.sql    30 Apr 2005 20:31:39 -0000    1.25
> --- src/test/regress/sql/type_sanity.sql    16 Jun 2005 01:37:09 -0000
> ***************
> *** 54,60 ****
>
>   SELECT p1.oid, p1.typname
>   FROM pg_type as p1
> ! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE '\\_%' AND NOT EXISTS
>       (SELECT 1 FROM pg_type as p2
>        WHERE p2.typname = ('_' || p1.typname)::name AND
>              p2.typelem = p1.oid);
> --- 54,60 ----
>
>   SELECT p1.oid, p1.typname
>   FROM pg_type as p1
> ! WHERE p1.typtype in ('b') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
>       (SELECT 1 FROM pg_type as p2
>        WHERE p2.typname = ('_' || p1.typname)::name AND
>              p2.typelem = p1.oid);
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
pgsql-patches by date:
From: Bruce Momjian
Date: 15 June 2005, 23:29:57
Subject: Escape handling in strings
From: Bruce Momjian
Date: 15 June 2005, 23:55:09
Subject: Re: Escape handling in strings
Re: Escape handling in strings - Mailing list pgsql-patches

Previous

Next
