Re: Error in crosstab using date_trunc

On 6 Dec 2009, at 4:13, Scott Marlowe wrote:

> On Sat, Dec 5, 2009 at 12:11 PM, Jose Maria Terry Jimenez
> <jtj(at)tssystems(dot)net> wrote:
>> Hello all,
>>
>> I'm trying to do a crosstab from data that row names are times.
>>
>> These times are timestamps and i want to use they truncating to minutes
>> this works for me:
>>
>> select distinct date_trunc('minute',"timestamp") as "timestamp" from
>> historico order by "timestamp";
>>
>> Getting times "normalized" without seconds.
>>
>> If i do a crosstab using that date_trunc function i get errors. If i do:
>>
>> select *
>> from crosstab
>> (
>> 'select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
>> historico order by 1,2'
>> )
>> as
>> (anet timestamp without time zone,
>> re1 numeric,
>> re2 numeric,
>> re3 numeric
>> )
>> ;
>
> Looks like an escaping issue. Try replacing your outer ' with $outer$
> or something like that:

It _is_ an escaping issue. The part inside the crosstab consists of 3 literals, namely:
'select date_trunc('
minute
',"timestamp") as "timestamp",remota,valor from historico order by 1,2'

You need to escape the quotes around 'minute', either by typing ''minute'' or by using a different quote method for the outer literal as Scott suggested. Your last option is the non-standard \' escaping.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b1b914911734630115167!