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

i do not have control over the columns...I have not designed the schema and do not have rights to change it — user3393089
– user3393089, Commented May 12, 2014 at 6:26

Craig Ringer · Accepted Answer · 2014-05-12 05:44:43Z

3

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.

answered May 12, 2014 at 5:44

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

3 Comments

user3393089 Over a year ago

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 Over a year ago

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 Over a year ago

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.

Gordon Linoff · Accepted Answer · 2014-05-12 05:39:08Z

1

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

answered May 12, 2014 at 5:39

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

postgres function error on execution

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related