CODEWITHSUNDEEP
sqlpostgresql
java
java

Reputation: 1214

time interval in function not working

I have a function:

CREATE OR REPLACE FUNCTION a(b integer)
  RETURNS integer AS
$BODY$ 
declare 
   c integer;
   timevalue text;
begin

   timevalue = b::text || ' days';   -- build string like '7 days'
   select sum(value)
   into c
   from tablx
   where createdate between current_date - interval timevalue and current_date;

return c;
end;
 $BODY$
  LANGUAGE plpgsql VOLATILE

function is simple... give summary of records which fits the date criteria. for some reason it does not accept current_date - interval timevalue

what can I do?

Upvotes: 0

Views: 530

Answers (1)

user330315
user330315

Reputation:

Unfortunately you can't specify a "dynamic" interval value. But as you always use the same unit, you can use:

select sum(value)
  into c
from tablx
where createdate between current_date - interval '1' day * b and current_date;

You can make that simpler, because you can subtract b directly from current_date

select sum(value)
  into c
from tablx
where createdate between current_date - b and current_date;

In an expression date - integer the integer value is the number of days.

For more details see the manual: http://www.postgresql.org/docs/current/static/functions-datetime.html

Upvotes: 2

Related Questions