diff options
| -rw-r--r-- | deploy/update_timestamp.sql | 15 | ||||
| -rw-r--r-- | revert/update_timestamp.sql | 9 | ||||
| -rw-r--r-- | sqitch.plan | 1 | ||||
| -rw-r--r-- | verify/update_timestamp.sql | 9 |
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; |