postgres function error on execution

Question

I have a function to delete those records withina given time period but I get an error while executing it.

10:58:23 [EXPLAIN - 0 row(s), 0.000 secs] [Error Code: 0, SQL State: 42883] ERROR: operator does not exist: date < integer
 Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
 Where: PL/pgSQL function delyearlyrecords(text) line 9 at EXECUTE statement
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

please find below the function

Create Or Replace Function DelYearlyRecords (tablename TEXT) Returns integer as $$
 Declare
 Start_Date date;
 End_Date date;
 Begin
 Start_Date = (current_date - interval '13 months')::date;
 End_Date = current_date;
 execute 'delete from ' ||tablename||
' where cast(date_dimension_year || ''-'' || date_dimension_month || ''-''||date_dimension_day as date) 
not between '|| Start_Date ||' and '|| End_Date || ' ';
 RETURN 1;
 END;
$$ LANGUAGE plpgsql;

Thanks & Regards, Rajeev

• user330315: Why aren't you using a real DATE column?
• user3393089: i do not have control over the columns...I have not designed the schema and do not have rights to change it

Answer 1

This is a horrible way to store dates in a table. Store them in the table as a date not as year, month and day integers.

What's happening is that

date_dimension_year || ''-'' || date_dimension_month || ''-''||date_dimension_day

evaluates to something like 2012年04月01日. Which is a mathematical expression on integers, producing a result like 2007.

What you really wanted was to prepend and append '', so you get '2012-04-01', which can be evaluated as date.

Better still, though, is to construct the date with intervals:

date_dimension_year * INTERVAL '1' YEAR + 
date_dimension_month * INTERVAL '1' MONTH + 
date_dimension_day * INTERVAL '1' DAY

... and then go fix your schema so you store the whole thing as date not a set of 3 integers in the first place so you don't have to jump through all these hoops.

• user3393089: I do not have control over the way data is stored I dint have any issues while running query as such but only when I used variables to store the values and then call it in the query later on did i get this error.
• user3393089: I was running it thhis way execute ' delete from '||tablename|| ' where now() - cast(date_dimension_year||''-''||date_dimension_month||''-''||date_dimension_day AS date) > INTERVAL ''13 months'''
• user3393089: I noticed that if i do the mathematical calculations and save it in variable instead of at the time of execution I was able to save a lot of time and so i was doing that.

Answer 2

In SQL, date constants are surrounded by single quotes. You need for these to be in the string. You can do this by including a double single quote in the string:

 execute 'delete from ' ||tablename||
' where cast(date_dimension_year || ''-'' || date_dimension_month || ''-''||date_dimension_day as date) 
not between '''|| Start_Date ||''' and '''|| End_Date || ''' ';