From 75e7a7044b389f86b556efd3ef7bde40512a3e23 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Sat, 26 May 2018 02:59:48 +0100 Subject: [PATCH] lib/postgres/url.sql --- .../{functions.sql => function/CONCAT.sql} | 0 lib/postgres/url.sql | 28 +++++++++++++++++++ 2 files changed, 28 insertions(+) rename lib/postgres/{functions.sql => function/CONCAT.sql} (100%) create mode 100644 lib/postgres/url.sql diff --git a/lib/postgres/functions.sql b/lib/postgres/function/CONCAT.sql similarity index 100% rename from lib/postgres/functions.sql rename to lib/postgres/function/CONCAT.sql diff --git a/lib/postgres/url.sql b/lib/postgres/url.sql new file mode 100644 index 00000000..c9735241 --- /dev/null +++ b/lib/postgres/url.sql @@ -0,0 +1,28 @@ +DROP TABLE IF EXISTS content; +CREATE TABLE IF NOT EXISTS content ( + id SERIAL PRIMARY KEY, + type VARCHAR(250) NOT NULL, + title VARCHAR(250) NOT NULL, + created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + content TEXT NOT NULL +); +CREATE INDEX IF NOT EXISTS content_created ON content USING btree (created); +CREATE INDEX IF NOT EXISTS content_type ON content USING btree (type); +CREATE INDEX IF NOT EXISTS content_title ON content USING btree (title); + +DROP FUNCTION IF EXISTS content_before_insert(); +/* +*/ +CREATE FUNCTION content_before_insert() RETURNS trigger as 'BEGIN + NEW.updated = now(); + RETURN NEW; +END' LANGUAGE 'plpgsql'; + +DROP TRIGGER IF EXISTS content_before_insert ON content; +/* +*/ +CREATE TRIGGER content_before_insert BEFORE INSERT OR UPDATE ON content +FOR EACH ROW +EXECUTE PROCEDURE content_before_insert() +; -- 2.47.3