-
Notifications
You must be signed in to change notification settings - Fork 1
/
migration.sql
53 lines (42 loc) · 1.37 KB
/
migration.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
--====================================
-- Create DB and table
--====================================
DROP DATABASE taskedo;
CREATE DATABASE taskedo;
--=======================================================
-- CONNECTO DB taskedo before launching these commands
--=======================================================
CREATE TABLE article (
"id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
"content" TEXT NOT NULL,
"created_at" TIMESTAMPTZ DEFAULT now(),
"is_done" BOOLEAN DEFAULT false
);
--====================================
-- Insert some values
--====================================
INSERT INTO article ("content", "created_at")
(
SELECT
('task' || ' ' || serie_nb),
(now() + interval '23 hours')
FROM generate_series( 1, 9) as serie_nb
);
--========================================
-- Create 2 functions create and update
--========================================
CREATE OR REPLACE FUNCTION create_article(JSON)
RETURNS TEXT AS $$
INSERT INTO "article"("content")
VALUES (($1 ->> 'content')::TEXT)
RETURNING "content";
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION update_article(JSON)
RETURNS TEXT AS $$
UPDATE "article"
SET
"content" = COALESCE(($1 ->> 'content')::TEXT,"content"),
"is_done" = COALESCE(($1 ->> 'is_done')::BOOLEAN,"is_done")
WHERE "id" = ($1 ->> 'id')::INT
RETURNING "content";
$$ LANGUAGE SQL;