From 516117207bbb3cca9723651b73941a2d1a5dca53 Mon Sep 17 00:00:00 2001 From: Masaya Tojo Date: Sun, 10 Mar 2024 20:31:56 +0900 Subject: Add function to update `updated_at` column. --- deploy/update_updated_at.sql | 15 +++++++++++++++ revert/update_updated_at.sql | 9 +++++++++ sqitch.plan | 1 + verify/update_updated_at.sql | 9 +++++++++ 4 files changed, 34 insertions(+) create mode 100644 deploy/update_updated_at.sql create mode 100644 revert/update_updated_at.sql create mode 100644 verify/update_updated_at.sql diff --git a/deploy/update_updated_at.sql b/deploy/update_updated_at.sql new file mode 100644 index 0000000..a41d27f --- /dev/null +++ b/deploy/update_updated_at.sql @@ -0,0 +1,15 @@ +-- Deploy diary:update_updated_at to pg +-- requires: schema + +BEGIN; + +SET search_path to diary; + +CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER AS $$ + BEGIN + NEW.updated_at = NOW(); + RETURN NEW; + END; +$$ LANGUAGE plpgsql; + +COMMIT; diff --git a/revert/update_updated_at.sql b/revert/update_updated_at.sql new file mode 100644 index 0000000..9ff67f8 --- /dev/null +++ b/revert/update_updated_at.sql @@ -0,0 +1,9 @@ +-- Revert diary:update_updated_at from pg + +BEGIN; + +SET search_path to diary; + +DROP FUNCTION update_updated_at; + +COMMIT; diff --git a/sqitch.plan b/sqitch.plan index eaacef8..af4c831 100644 --- a/sqitch.plan +++ b/sqitch.plan @@ -3,3 +3,4 @@ %uri=https://git.tojo.tokyo/diary-schema.git/about/ schema 2024-03-10T11:00:19Z Masaya Tojo # Add schema for diary objects. +update_updated_at [schema] 2024-03-10T11:29:05Z Masaya Tojo # Add function to update `updated_at` column. diff --git a/verify/update_updated_at.sql b/verify/update_updated_at.sql new file mode 100644 index 0000000..59c71be --- /dev/null +++ b/verify/update_updated_at.sql @@ -0,0 +1,9 @@ +-- Verify diary:update_updated_at on pg + +BEGIN; + +SET search_path to diary; + +SELECT has_function_privilege('update_updated_at()', 'EXECUTE'); + +ROLLBACK; -- cgit v1.2.3