Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
    CREATE OR REPLACE FUNCTION deleteWeekAggTables (tablename TEXT, Duration TEXT) RETURNS INTEGER as $$

DECLARE
    startWeek INTEGER;
    endWeek INTEGER;
BEGIN
    startWeek=  EXTRACT(YEAR FROM  (CURRENT_DATE - INTERVAL ''||Duration||'') ) * 100 + EXTRACT(WEEK FROM (CURRENT_DATE - INTERVAL ''||Duration||'')) ;
    endWeek =  EXTRACT(YEAR FROM CURRENT_DATE) * 100 + EXTRACT(WEEK FROM CURRENT_DATE) ;

    EXECUTE ' DELETE FROM '||tablename||'
                    WHERE date_dimension_year * 100 + date_dimension_week
                  NOT BETWEEN '||startWeek||'  AND '||endWeek||' ' ;
        RETURN 1;
       END;
$$ LANGUAGE plpgsql;

I get error on calling this function I dont know what is the issue here as per logic it should pas the duration as parameter

    6  [SELECT - 0 row(s), 0.000 secs]  [Error Code: 0, SQL State: 22007]  ERROR: invalid input syntax for type interval: ""
  Where: PL/pgSQL function deleteweekaggtables(text,text) line 7 at assignment
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

on calling the function :

select deleteWeekAggTables('raj_weekly','13 months');

Ok it works now although I have a similar problem with another function :

    Create Or Replace Function DelAggRecords (tablename TEXT, Duration interval) Returns integer as $$

Begin
execute ' delete from '||tablename|| ' 
where now() - cast(date_dimension_year||''-''||date_dimension_month||''-''||date_dimension_day AS date) > 'Duration;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

select DelAggRecords('raj_test','13 months'::interval);
share|improve this question

1 Answer 1

up vote 1 down vote accepted

The easiest is to pass an interval to the function

select deleteWeekAggTables('raj_weekly','13 months'::interval);

create or replace function deleteweekaggtables (
    tablename text, duration interval
) returns integer as $$

startweek = 
    extract(year from  (current_date - duration) ) * 100 + 
    extract(week from (current_date - duration)) ;
share|improve this answer
    
thanks it worked but I am trying this in a similar function and fails I tried different ways but no solution –  user3393089 Jul 31 '14 at 11:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.