Issue in creating a function in PostgreSQL using date_trunc

Question

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

Well that could be a better formatted question.

David Aldridge
– David Aldridge

2014-02-07 12:11:52 +00:00
Commented Feb 7, 2014 at 12:11 — David Aldridge
– David Aldridge, Commented Feb 7, 2014 at 12:11

Houari · Accepted Answer · 2014-02-07 12:24:32Z

1

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

answered Feb 7, 2014 at 12:24

Houari

5,6513 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

... all of which is much easier to read when you use EXECUTE ... USING to supply query parameters, and format identifiers with format('SELECT ... FROM %I ...', tablename) etc. Yet everyone keeps on using string concatenation and quote_literal.

Collectives™ on Stack Overflow

Issue in creating a function in PostgreSQL using date_trunc

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related