PostgreSQL: month: = interval '30 days';

Question

Trying to delete records older than 1 month from 2 tables, where 1 refers to the id column in another:

create or replace function quincytrack_clean() returns void as $BODY$ begin month := interval '30 days'; delete from hide_id where id in (select id from quincytrack where age(QDATETIME) > month); delete from quincytrack where age(QDATETIME) > month; end; $BODY$ language plpgsql; 

but this fails:

 ERROR: syntax error at or near "month" LINE 1: month := interval '30 days' ^ QUERY: month := interval '30 days' CONTEXT: SQL statement in PL/PgSQL function "quincytrack_clean" near line 2 

I am reading a document , but I don’t understand what is wrong with my declaration ...

Answer 1

You need to declare a month variable, namely:

 declare month interval; begin month := interval '30 days'; end; 

In addition, you can revise your "where" criteria. If QDATETIME is an indexed column, I don't think it will use the index, while QDATETIME < (now() - month) will.

Answer 2

You need to declare a variable before you can use it.

 ...
 Decare
    month INTERVAL;
 BEGIN 
    month: = interval '30 days';
  ...

But I would avoid using variable names that are reserved words or names of internal functions.