Add function to update `updated_at` column.

4 files changed, 34 insertions, 0 deletions

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 <masaya@tojo.tokyo> # Add schema for diary objects.
+update_updated_at [schema] 2024-03-10T11:29:05Z Masaya Tojo <masaya@tojo.tokyo> # 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;