Postgresql
如何使用 WHERE 子句比較表中生成的觸發器值發出 DML?
假設我有一個 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 );