PostgreSQL:month:= interval '30 days'; [英] PostgreSQL: month := interval '30 days';

问题描述

尝试从2个表中删除超过1个月的记录，其中1个引用另一个表中的"id"列:

Trying to delete records older than 1 month from 2 tables, where 1 references 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;

但这失败了:

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'm reading the doc, but don't understand what's wrong with my declaration...

推荐答案

您需要声明变量"month"，即:

You need to declare the variable 'month', viz.:

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

此外，您可能想重新检查您的哪里"条件.如果QDATETIME是索引列，则我认为它不会使用索引，而QDATETIME < (now() - month)会使用索引.

Also, you might want to re-examine your "where" criteria. If QDATETIME is an indexed column, I don't think it will use the index, whereas QDATETIME < (now() - month) would.

这篇关于PostgreSQL:month:= interval '30 days';的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！