Add function to update timestamp

4 files changed, 34 insertions, 0 deletions

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 <masaya@tojo.tokyo> # Add schema for all photos objects.
 photos [schema] 2023-08-29T13:12:25Z Masaya Tojo <masaya@tojo.tokyo> # Create table to manage my photos.
+update_timestamp [schema] 2023-08-29T15:03:35Z Masaya Tojo <masaya@tojo.tokyo> # 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;