sql - PostgreSQL 函数：错误运算符不存在

Question

我正在尝试编写一个函数，该函数可以删除date_informationcolumn 低于now() - interval '2 months'.

当我尝试更新一行时出现此问题：

sql> UPDATE "public"."token_jwt_usertoken" SET "date_information" = '2020/02/08' WHERE "id" = 1
[2019-02-08 09:27:56] [42883] ERROR: operator does not exist: character varying < timestamp with time zone
[2019-02-08 09:27:56] Indice : No operator matches the given name and argument type(s). You might need to add explicit type casts.
[2019-02-08 09:27:56] Où : PL/pgSQL function after_update_token() line 3 at IF

这是我的功能：

CREATE FUNCTION after_update_token() RETURNS TRIGGER AS $after_update_token$
  BEGIN
    IF to_timestamp(old.date_information, 'YYYY/MM/DD') < now() - INTERVAL '2 months'
      THEN
        DELETE FROM token_jwt_usertoken WHERE to_timestamp(old.date_information, 'YYYY/MM/DD') < now() - INTERVAL '2 months';
    END IF;
END;

我的桌子看起来像这样：

id  token  user_id  period_duration  date_information

2   eyJ0    318 1y  2018/02/07

如何在我使用old.date_informationfield 的地方正确重写我的函数？

谢谢

编辑 ：

我编辑了类型date_information以将其设置为date类型。

代码变为：

CREATE FUNCTION after_update_token() RETURNS TRIGGER AS $after_update_token$
  BEGIN
    DELETE FROM token_jwt_usertoken WHERE old.date_information < now() - INTERVAL '2 months';
    RETURN NULL;
  END;

$after_update_token$
LANGUAGE plpgsql;

CREATE TRIGGER after_update_token AFTER UPDATE ON token_jwt_usertoken FOR EACH ROW EXECUTE PROCEDURE after_update_token();

它返回这个问题：

[2019-02-08 10:06:41] [2F005] 错误：控制到达触发过程结束而没有返回 [2019-02-08 10:06:41] Où：PL/pgSQL 函数 after_update_token()

这是 DDL：

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.11
-- Dumped by pg_dump version 11.1 (Ubuntu 11.1-1.pgdg18.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: token_jwt_usertoken; Type: TABLE; Schema: public; Owner: ocabr_user
--

CREATE TABLE public.token_jwt_usertoken (
    id integer NOT NULL,
    token character varying(256) NOT NULL,
    user_id integer NOT NULL,
    period_duration character varying(50) NOT NULL,
    date_information date
);


ALTER TABLE public.token_jwt_usertoken OWNER TO ocabr_user;

--
-- Name: token_jwt_usertoken_id_seq; Type: SEQUENCE; Schema: public; Owner: ocabr_user
--

CREATE SEQUENCE public.token_jwt_usertoken_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.token_jwt_usertoken_id_seq OWNER TO ocabr_user;

--
-- Name: token_jwt_usertoken_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ocabr_user
--

ALTER SEQUENCE public.token_jwt_usertoken_id_seq OWNED BY public.token_jwt_usertoken.id;


--
-- Name: token_jwt_usertoken id; Type: DEFAULT; Schema: public; Owner: ocabr_user
--

ALTER TABLE ONLY public.token_jwt_usertoken ALTER COLUMN id SET DEFAULT nextval('public.token_jwt_usertoken_id_seq'::regclass);


--
-- Data for Name: token_jwt_usertoken; Type: TABLE DATA; Schema: public; Owner: ocabr_user
--

--
-- Name: token_jwt_usertoken_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ocabr_user
--

SELECT pg_catalog.setval('public.token_jwt_usertoken_id_seq', 6, true);


--
-- Name: token_jwt_usertoken token_jwt_usertoken_pkey; Type: CONSTRAINT; Schema: public; Owner: ocabr_user
--

ALTER TABLE ONLY public.token_jwt_usertoken
    ADD CONSTRAINT token_jwt_usertoken_pkey PRIMARY KEY (id);


--
-- Name: token_jwt_usertoken token_jwt_usertoken_user_id_0de4884e_uniq; Type: CONSTRAINT; Schema: public; Owner: ocabr_user
--

ALTER TABLE ONLY public.token_jwt_usertoken
    ADD CONSTRAINT token_jwt_usertoken_user_id_0de4884e_uniq UNIQUE (user_id);


--
-- Name: token_jwt_usertoken token_jwt_usertoken_user_id_0de4884e_fk_auth_user_id; Type: FK CONSTRAINT; Schema: public; Owner: ocabr_user
--

ALTER TABLE ONLY public.token_jwt_usertoken
    ADD CONSTRAINT token_jwt_usertoken_user_id_0de4884e_fk_auth_user_id FOREIGN KEY (user_id) REFERENCES public.auth_user(id) DEFERRABLE INITIALLY DEFERRED;


--
-- PostgreSQL database dump complete
--

执行的更新命令是：

UPDATE "public"."token_jwt_usertoken" SET "date_information" = '2021-02-07' WHERE "id" = 3;

标签： sqlpostgresql