postgres function not excuting properly: syntax error

Question

    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);

Clodoaldo Neto · Accepted Answer · 2014-07-31 10:16:23Z

1

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)) ;

answered Jul 31, 2014 at 10:16

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

user3393089 Over a year ago

thanks it worked but I am trying this in a similar function and fails I tried different ways but no solution

Collectives™ on Stack Overflow

postgres function not excuting properly: syntax error

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