Re: [SQL] the use of $$string$$

[John Fabiani]

On Friday, November 04, 2011 11:06:37 am Richard Huxton wrote:
> 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

thanks for the reply.

I'm using psycopg2.

This is what I'm doing from python
myvarString = "long string that contains single quotes"
cusor.execute("insert into table (pkid, myfield) values (%s, $$%s$$)",(123, 
myvarString))

When I execute the above I'm seeing:
E'long string that contains single quotes' in the field.  When I do a "select 
* from table"   I get E'long string that contains single quotes'.

If I do

myvarString = "long string that without single quotes"
cusor.execute("insert into table (pkid, myfield) values (%s, %s)",(123, 
myvarString))

I get the following:
 "long string that without single quotes"

I have to be doing something wrong. But where?

Johnf

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How to implement Aggregate Awareness?

[Craig Ringer]

On 11/05/2011 05:03 AM, Olgierd Michalak wrote:


Simply put, when Transparent (to the reporting tool) Aggregate Navigator
recognizes that a query would execute faster using aggregates, it
automatically rewrites the query so that the database hits the smaller
aggregates rather than larger detail tables upon which the small
aggregates are pre-computed.


Assuming I understand what you want correctly: Before you could usefully 
do anything like that, I think you'd need to implement full support for 
materialized views.


Once that's in place, the optimizer could potentially be enhanced to 
recognise queries against a base table and rewrite it to use a 
materialized view of the table when it sees that the query only touches 
data collected by the materialized view.


Right now, there isn't really anything for query rewriting like this to 
/target/ .


--
Craig Ringer

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql