CODEWITHSUNDEEP
postgresql
Abdul
Abdul

Reputation: 107

Issue in creating a function in PostgreSQL using date_trunc

Here is a sample of my code

v_sql_main:= ' SELECT min_createdate, max_createdate, createdate, customerid::integer, deviceid::integer, null::bigint as sourceip, null::bigint as sourceip_int, service,  total, end_recordid::bigint '||
 ' FROM ( '||
 ' SELECT min(date_trunc( '||quote_literal('HOUR')||' , firstoccurrence)) as  min_createdate, '||
 '        max(date_trunc( '||quote_literal('HOUR')||' , firstoccurrence)) as  max_createdate, '||
 '        date_trunc( '||quote_literal('DAY')||' , firstoccurrence) as  createdate,  '||
 '        customerid::integer,  '||
 '        deviceid::integer, '||
 '        service, '||
 case  when v_days < 4 then 
 '        count(1)  as   total '
 else
 '        sum(summcount)  as   total '
 end ||', max(recordid) as end_recordid'
 ' FROM   '|| v_tablename||
 ' LEFT OUTER JOIN '|| v_child_tablename||
 ' ON ' ||v_tablename||'.SERVICE_ID = '|| v_child_tablename||'.SERVICE_ID '||
 '        WHERE  '||
 '             customerid = v_customerid   AND '||
 '             deviceid   = v_deviceid     AND '||
 '             date_trunc( '||quote_literal('DAY')||' , firstoccurrence) = date_trunc( '||quote_literal('DAY')||' ,now()- interval '1 day') '||
 ' group by date_trunc( '||quote_literal('DAY')||' , firstoccurrence),  customerid, deviceid, service ) as a order by total desc limit 10;;';

When I try to execute this I am getting the following error

ERROR:  syntax error at or near "1"
LINE 144: ...unc( '||quote_literal('DAY')||' ,now()- interval '1 day') '|...

What i need is to get: date-1

Thanks in Advance SHABEER

Upvotes: 0

Views: 152

Answers (1)

Houari
Houari

Reputation: 5631

Replace the line:

 '             date_trunc( '||quote_literal('DAY')||' , firstoccurrence) = date_trunc( '||quote_literal('DAY')||' ,now()- interval '1 day') '||

by:

'             date_trunc( '||quote_literal('DAY')||' , firstoccurrence) = date_trunc( '||quote_literal('DAY')||' ,now()- interval '' 1 day'') '||

Please take a look to the Interval syntax documentation

Upvotes: 1

Related Questions