Message-ID: <4EB429AD.3030607@archonet.com>
Date: Fri, 04 Nov 2011 18:06:37 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.23) Gecko/20110921 Lightning/1.0b2 Thunderbird/3.1.15
MIME-Version: 1.0
To: John Fabiani <johnf@jfcomputer.com>
CC: pgsql-sql@postgresql.org
Subject: Re: the use of $$string$$
References: <201111040738.29303.johnf@jfcomputer.com>
	<201111040826.16177.johnf@jfcomputer.com>
In-Reply-To: <201111040826.16177.johnf@jfcomputer.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 04/11/11 15:26, John Fabiani wrote:
> On Friday, November 04, 2011 07:38:29 am John Fabiani wrote:
>> Hi,
>> I just discovered that I can use $$string$$ to account for the problem of
>> single quotes in the string (or other strange char's).  However, I noticed
>> that the table field contained E'string'.  I actually tried to find info on
>> this but I did not find anything.
>>
>> Could someone explain what it means or better provide a web link for me to
>> discover the info.

> Thank you both for the quick replies and the links.
>
> What I still do not understand (I'm a little slow) is the fact that pgadmin3
> showed the data as E'string'.  So I'm wondering if the data is now different
> in the sense that I need use the 'E' in the field.

I think you're missing some bit of the puzzle here.

There's the universal problem of how to deal with quote marks inside 
quotes. The SQL standard says you double the quote.

SELECT length('ab''de');
  length
--------
       5

Traditionally, PostgreSQL let you use backslash escapes too, not just 
for single quotes but for some other common C-style idioms.

SELECT length(E'ab\'de');
  length
--------
       5

For the last few years, this has been moved into its own quoting style 
so standard strings are always well, standard.

SELECT length('ab\nde');
  length
--------
       6

SELECT length(E'ab\nde');
  length
--------
       5

The [E'] is an opening quote - both characters. It isn't part of the 
value at all. If a field contains "E'" then you put it there, possibly 
by quoting something in pgadmin that was already quoted.

Likewise you can use $$..$$ to quote strings (actually $$whatever$$). 
That gives you sql-standard escaping apart from single quotes. It's 
especially useful for function bodies which tend to contain their own 
string literals.

SELECT length($$ab\nde$$);
  length
--------
       6

None of this should matter from an application however, since its 
database access library should do all the quoting for you.

HTH

-- 
   Richard Huxton
   Archonet Ltd