From 6d6f4546b0cda266e6f5e6f2cda783da985dfb00 Mon Sep 17 00:00:00 2001 From: Masaya Tojo Date: Wed, 30 Aug 2023 00:10:06 +0900 Subject: Add function to update timestamp --- deploy/update_timestamp.sql | 15 +++++++++++++++ revert/update_timestamp.sql | 9 +++++++++ sqitch.plan | 1 + verify/update_timestamp.sql | 9 +++++++++ 4 files changed, 34 insertions(+) create mode 100644 deploy/update_timestamp.sql create mode 100644 revert/update_timestamp.sql create mode 100644 verify/update_timestamp.sql diff --git a/deploy/update_timestamp.sql b/deploy/update_timestamp.sql new file mode 100644 index 0000000..7bcec3c --- /dev/null +++ b/deploy/update_timestamp.sql @@ -0,0 +1,15 @@ +-- Deploy photos:update_timestamp to pg +-- requires: schema + +BEGIN; + +SET search_path to photos; + +CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$ + BEGIN + NEW.updated_at = NOW(); + RETURN NEW; + END; +$$ LANGUAGE plpgsql; + +COMMIT; diff --git a/revert/update_timestamp.sql b/revert/update_timestamp.sql new file mode 100644 index 0000000..cbbf612 --- /dev/null +++ b/revert/update_timestamp.sql @@ -0,0 +1,9 @@ +-- Revert photos:update_timestamp from pg + +BEGIN; + +SET search_path to photos; + +DROP FUNCTION update_timestamp; + +COMMIT; diff --git a/sqitch.plan b/sqitch.plan index f3a1a38..7362c06 100644 --- a/sqitch.plan +++ b/sqitch.plan @@ -4,3 +4,4 @@ schema 2023-08-27T13:52:33Z Masaya Tojo # Add schema for all photos objects. photos [schema] 2023-08-29T13:12:25Z Masaya Tojo # Create table to manage my photos. +update_timestamp [schema] 2023-08-29T15:03:35Z Masaya Tojo # Add function to update timestamp diff --git a/verify/update_timestamp.sql b/verify/update_timestamp.sql new file mode 100644 index 0000000..df5609d --- /dev/null +++ b/verify/update_timestamp.sql @@ -0,0 +1,9 @@ +-- Verify photos:update_timestamp on pg + +BEGIN; + +SET search_path to photos; + +SELECT has_function_privilege('update_timestamp()', 'EXECUTE'); + +ROLLBACK; -- cgit v1.2.3