我想要一個觸發器來洗掉他們居住超過 10 分鐘的現有記錄
drop table if exists authorization_code;
create table if not exists authorization_code(
id int generated always as identity,
created_at timestamptz not null
);
drop function if exists remove_expired();
create function remove_expired()
returns trigger
language plpgsql
as $$
begin
--NOTE 10 mins as recommended by the OAuth2 spec
delete from authorization_code where now() - created_at > 600;
return NEW;
end;
$$;
drop trigger if exists clean_up_expired_code on authorization_code;
create trigger clean_up_expired_code
before insert
on authorization_code
for each row
execute procedure remove_expired();
但是現在如果我插入，我會得到這樣的錯誤：
sso=# insert into authorization_code(created_at) values(now());
ERROR: operator does not exist: interval > integer
LINE 1: ...lete from authorization_code where now() - created_at > 600
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY: delete from authorization_code where now() - created_at > 600
CONTEXT: PL/pgSQL function remove_expired() line 4 at SQL statement
實作我想要的正確方法是什么？
uj5u.com熱心網友回復：
嘗試使用 remove_expired() 函式的這個修改版本
halley=> \sf remove_expired
CREATE OR REPLACE FUNCTION public.remove_expired()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
--NOTE 10 mins as recommended by the OAuth2 spec
delete from authorization_code where EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)-EXTRACT(EPOCH FROM created_at) > 600;
return NEW;
end;
$function$
uj5u.com熱心網友回復：
減去兩個時間戳的結果是一個，interval因此您可以直接比較：
where now() - created_at > interval '10 minutes';
或者，如果您想提供持續時間作為指示秒數的引數：
where now() - created_at > make_interval(secs => 600);
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/343251.html
標籤：PostgreSQL
上一篇：如何在postgres中選擇當月的第一天和下個月的第一天（從當月開始）
下一篇：獲取不同子組中的值計數