Postgresql

如何使用 WHERE 子句比較表中生成的觸發器值發出 DML?

  • December 13, 2019

假設我有一個 CREATE 表,

CREATE TABLE TEST (
   year int,
   month int, 
   date int, 
   hr int, 
   min int, 
   sec int, 
   timestamp timestamp,
   value double
);

CREATE FUNCTION timestamp_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$

   DECLARE datestr TEXT ;
   DECLARE timestr TEXT ;
   begin
       datestr :=  new.year || '-' || new.month || '-' || new.date || ' ' ||  new.hour || ':' || new.min || ':' || new.sec;+ 
       new.timestamp := datestr :: TIMESTAMP                                                                      
       return new;
   end;
$$
CREATE TRIGGER timestamp_insert BEFORE INSERT OR UPDATE ON TEST FOR EACH ROW EXECUTE PROCEDURE timestamp_insert();

現在我想根據目前表上的時間戳和一組新數據更新某一行。就像是,

UPDATE TEST SET value = ? WHERE timestamp < "generated timestamp from a new set of (year, month, date, ...)"

是否可以在 SQL 中執行類似的操作,或者我是否需要以程式方式生成時間戳,然後在 SQL 中簡單地傳遞該值?

有一個名為make_timestamp()您可以使用的函式。它還使您的觸發功能更短:

CREATE FUNCTION timestamp_insert() 
 RETURNS trigger
 LANGUAGE plpgsql
AS 
$$
begin
   new.timestamp := make_timestamp(new.year, new.month, new.date, new.hour, new.min, new.sec);
   return new;
end;
$$

同樣的功能也可以在 UPDATE 中使用

UPDATE TEST 
  SET value = ? 
WHERE timestamp < make_timestamp(year, month, date, hour, min, sec);

如果您使用的是 Postgres 12,則不需要觸發器,因為它現在支持生成/計算的列:

CREATE TABLE TEST 
(
 year int,
 month int, 
 date int, 
 hr int, 
 min int, 
 sec int, 
 ts timestamp generated always as (make_timestamp(year, month, date, hr, min, sec)) stored,
 value double precision
);

引用自:https://dba.stackexchange.com/questions/255551