· 6 years ago · Oct 05, 2019, 07:32 PM
1CREATE DATABASE test_db;
2
3/*
4 1.
5 There is a table Т with columns А, В, С.
6 Need to remove all duplicates, that only last entries with unique А, В, С are remain.
7*/
8
9
10CREATE TABLE table_t
11(
12 column_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
13 column_a TEXT NOT NULL,
14 column_b TEXT NOT NULL,
15 column_c TEXT NOT NULL
16);
17
18
19/* 1 - GENERATING AND DUPLICATING DATA :: BEGIN */
20-- generate data
21INSERT INTO table_t (column_a, column_b, column_c)
22SELECT md5(random()::text) AS column_a, md5(random()::text) AS column_b, md5(random()::text) AS column_c
23FROM generate_series(1, 100) s(i);
24
25-- no duplicates from data generation
26SELECT tt1.*
27FROM table_t AS tt1
28INNER JOIN (SELECT
29 itt1.column_id, itt1.column_a, itt1.column_b, itt1.column_c,
30 ROW_NUMBER() OVER(PARTITION BY itt1.column_a, itt1.column_b, itt1.column_c ORDER BY itt1.column_a, itt1.column_b, itt1.column_c, itt1.column_id) AS row_num
31 FROM table_t AS itt1
32 INNER JOIN (SELECT
33 iitt.column_a, iitt.column_b, iitt.column_c, COUNT(*) AS count_of
34 FROM table_t AS iitt
35 GROUP BY iitt.column_a, iitt.column_b, iitt.column_c
36 HAVING COUNT(*) > 1
37 ) itt2 ON itt1.column_a=itt2.column_a AND itt1.column_b=itt2.column_b AND itt1.column_c=itt2.column_c
38 ) tt2 ON tt1.column_id=tt2.column_id
39WHERE tt2.row_num!=1;
40
41-- duplicate all records
42INSERT INTO table_t (column_a, column_b, column_c)
43SELECT column_a, column_b, column_c
44FROM table_t;
45/* 1 - GENERATING AND DUPLICATING DATA :: END */
46
47
48/* 1 - DELETE ALL DUPLICATED DATA :: BEGIN */
49-- to perserve only last entries, order by id column is done in descencing order - while partitioning
50DELETE FROM table_t
51WHERE table_t.column_id IN
52(
53SELECT tt1.column_id
54FROM table_t AS tt1
55INNER JOIN (
56 SELECT itt1.column_id, itt1.column_a, itt1.column_b, itt1.column_c,
57 ROW_NUMBER() OVER (PARTITION BY itt1.column_a, itt1.column_b, itt1.column_c ORDER BY itt1.column_id DESC, itt1.column_a, itt1.column_b, itt1.column_c) AS row_num
58 FROM table_t AS itt1
59 INNER JOIN (
60 SELECT iitt.column_a, iitt.column_b, iitt.column_c, COUNT(*) AS count_of
61 FROM table_t AS iitt
62 GROUP BY iitt.column_a, iitt.column_b, iitt.column_c
63 HAVING COUNT(*) > 1
64 ) itt2 ON itt1.column_a=itt2.column_a AND itt1.column_b=itt2.column_b AND itt1.column_c=itt2.column_c
65 ) tt2 ON tt1.column_id=tt2.column_id
66WHERE tt2.row_num!=1
67)
68RETURNING *;
69/* 1 - DELETE ALL DUPLICATED DATA :: END */
70
71
72/* 1 - CHECK FOR REMAINING DATA :: BEGIN */
73--only last entries remains
74SELECT *
75FROM table_t;
76/* 1 - DELETE ALL DUPLICATED DATA :: END */
77
78
79
80/*
81 2.
82 There is a table of non-intersecting intervals T ( id bigint not null, start_date timestamptz, end_date timestamptz ).
83 For example, assigning employees to positions (two assignments on one position cannot be at the same time) or action price period
84 (similarly, two prices cannot be simultaneous) - this is irrelevant, just for understanding..
85 The task: create a function for (a) adding and (b) modifying of intervals, provided that they must not intersect.
86*/
87
88
89CREATE TABLE IF NOT EXISTS non_intersecting_intervals
90(
91 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
92 start_date TIMESTAMP NOT NULL,
93 end_date TIMESTAMP NOT NULL
94);
95
96
97/* 2 - CREATE TRIGGER :: BEGIN */
98CREATE OR REPLACE FUNCTION non_intersecting_intervals__before__insert_update()
99RETURNS TRIGGER
100AS $$
101BEGIN
102
103 IF NEW.start_date IS NULL THEN
104 RAISE EXCEPTION 'start_date can not be null';
105 END IF;
106 IF NEW.end_date IS NULL THEN
107 RAISE EXCEPTION 'end_date can not be null';
108 END IF;
109
110 IF NEW.start_date > NEW.end_date THEN
111 RAISE EXCEPTION '% is older then %', NEW.start_date, NEW.end_date;
112 END IF;
113
114 IF (TG_OP = 'INSERT') THEN
115 IF EXISTS (SELECT nii.id FROM non_intersecting_intervals AS nii WHERE (nii.start_date, nii.end_date) OVERLAPS (NEW.start_date, NEW.end_date)) THEN
116 RAISE EXCEPTION '% and % are overlaping with other time intervals', NEW.start_date, NEW.end_date;
117 END IF;
118 ELSIF (TG_OP = 'UPDATE') THEN -- in case of update do not check record we want to update
119 IF EXISTS (SELECT nii.id FROM non_intersecting_intervals AS nii WHERE nii.id != NEW.id AND (nii.start_date, nii.end_date) OVERLAPS (NEW.start_date, NEW.end_date)) THEN
120 RAISE EXCEPTION '% and % are overlaping with other time intervals', NEW.start_date, NEW.end_date;
121 END IF;
122 END IF;
123
124 RETURN NEW;
125
126END;
127$$ LANGUAGE plpgsql;
128
129
130
131CREATE TRIGGER non_intersecting_intervals_audit
132BEFORE INSERT OR UPDATE ON non_intersecting_intervals
133FOR EACH ROW EXECUTE PROCEDURE non_intersecting_intervals__before__insert_update();
134/* 2 - CREATE TRIGGER :: END */
135
136
137/* 2 - CREATE INSERT FUNCTION :: BEGIN */
138CREATE OR REPLACE FUNCTION "public"."non_intersecting_intervals__insert"(p_start_date TIMESTAMP, p_end_date TIMESTAMP)
139RETURNS SETOF non_intersecting_intervals
140AS $$
141BEGIN
142
143 RETURN QUERY
144 INSERT INTO non_intersecting_intervals (start_date, end_date)
145 VALUES (p_start_date, p_end_date)
146 RETURNING *;
147
148END;
149$$ LANGUAGE plpgsql;
150/* 2 - CREATE INSERT FUNCTION :: END */
151
152
153/* 2 - CREATE UPDATE FUNCTION :: BEGIN */
154CREATE OR REPLACE FUNCTION "public"."non_intersecting_intervals__update"(p_id INTEGER, p_start_date TIMESTAMP, p_end_date TIMESTAMP)
155RETURNS SETOF non_intersecting_intervals
156AS $$
157BEGIN
158
159 RETURN QUERY
160 UPDATE non_intersecting_intervals
161 SET start_date = p_start_date, end_date = p_end_date
162 WHERE id = p_id
163 RETURNING *;
164
165END;
166$$ LANGUAGE plpgsql;
167/* 2 - CREATE UPDATE FUNCTION :: END */
168
169
170
171/* 2 - TEST CASES :: BEGIN */
172
173SELECT * FROM "public"."non_intersecting_intervals__insert"(CAST('29-09-2019' AS TIMESTAMP), CAST('30-10-2019' AS TIMESTAMP));
174
175INSERT INTO non_intersecting_intervals (start_date, end_date)
176VALUES (CAST('30-09-2019' AS TIMESTAMP), NULL);
177
178SELECT * FROM "public"."non_intersecting_intervals__insert"(CAST('30-09-2019' AS TIMESTAMP), CAST('29-10-2019' AS TIMESTAMP));
179
180INSERT INTO non_intersecting_intervals (start_date, end_date)
181VALUES (NULL, CAST('30-09-2019' AS TIMESTAMP));
182
183SELECT * FROM "public"."non_intersecting_intervals__insert"(CAST('29-10-2019' AS TIMESTAMP), CAST('30-09-2019' AS TIMESTAMP));
184
185INSERT INTO non_intersecting_intervals (start_date, end_date)
186VALUES (CAST('30-01-2019' AS TIMESTAMP), CAST('28-02-2019' AS TIMESTAMP));
187
188SELECT * FROM "public"."non_intersecting_intervals__insert"(CAST('29-01-2018' AS TIMESTAMP), CAST('30-09-2018' AS TIMESTAMP));
189
190SELECT * FROM "public"."non_intersecting_intervals__update"(7, CAST('29-11-2018' AS TIMESTAMP), CAST('30-11-2018' AS TIMESTAMP));
191
192/* 2 - TEST CASES :: END */
193
194
195
196/*
197 3. There is a constant stream of some events for logging (suppose, the source_name, event_name, service_data in json).
198 Periodically (once a week) old data (older than 1 month) must be moved to the archive (another table) and finally deleted after 3 months.
199 At the same time, there is no way to interrupt or somehow delay the flow.
200 Access to current data should be all this time without having to rewrite select (i.e., they can always be obtained from the same table).
201 The task: create (a) table (tables), (b) functions for archiving and (c) deleting old data.
202*/
203
204--pgAgent is a job scheduling agent for Postgres databases, capable of running multi-step batch or shell scripts and SQL tasks on complex schedules.
205CREATE EXTENSION pgagent;
206
207CREATE TABLE logs
208(
209 log_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
210 source_name TEXT NOT NULL,
211 event_name TEXT NOT NULL,
212 service_data JSON NOT NULL,
213 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
214);
215
216CREATE TABLE logs_archive
217(
218 log_id BIGINT NOT NULL,
219 source_name TEXT NOT NULL,
220 event_name TEXT NOT NULL,
221 service_data JSON NOT NULL,
222 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
223);
224
225
226-- INSIDE THIS PG AGENT JOB IS UNSAFE APPROACH OF ARCHIVING, IF INSERT STATMENT EXECUTION IS FEW SECONDS,
227-- WE CAN LEAVE SOME OF THE LOGS INSIDE LOG TABLE (IF THERE ARE SOME LOGS CATCHED AT THAT EXACT SAME TIME)
228-- FUNCTIONS BELOW DEMONSTRATE DELETING ONLY WHAT WE INSERTED IN ARCHIVE
229
230/* 3 - PGAGENT - JOB CREATION :: BEGIN */
231UPDATE pgagent.pga_jobstep
232SET jstcode =
233'INSERT INTO logs_archive (log_id, source_name, event_name, service_data, created_at)
234SELECT l.log_id, l.source_name, l.event_name, l.service_data, l_created_at
235FROM logs AS l
236WHERE l.created_at <= NOW() - interval ''1 month'';
237
238DELETE
239FROM logs AS l
240WHERE l.created_at <= NOW() - interval ''1 month'';'
241WHERE jstid=1::integer AND jstjobid=1::integer;
242
243INSERT INTO pgagent.pga_jobstep (
244 jstjobid, jstname, jstenabled, jstkind,
245 jstconnstr, jstdbname, jstonerror,
246 jstcode, jstdesc
247)
248VALUES
249(
2501, 'logs_archive__delete_3months_old_data'::text, true, 's'::character(1), ''::text, 'mop_testdb'::name, 'f'::character(1),
251'DELETE
252FROM logs_archive AS la
253WHERE la.created_at <= NOW() - interval ''3 month'';'::text, ''::text
254) RETURNING jstid;
255
256DO $$
257DECLARE
258 scid integer;
259BEGIN
260-- Inserting a schedule (jobid: 1)
261INSERT INTO pgagent.pga_schedule(
262 jscjobid, jscname, jscdesc, jscenabled,
263 jscstart, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
264) VALUES (
265 1, 'weekly__sunday_04_00'::text, ''::text, true,
266 '2019-10-01 02:00:00 +02:00'::timestamp with time zone,
267 -- Minutes
268 ARRAY[true,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false]::boolean[],
269 -- Hours
270 ARRAY[false,false,false,false,true,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false]::boolean[],
271 -- Week days
272 ARRAY[true,false,false,false,false,false,false]::boolean[],
273 -- Month days
274 ARRAY[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false]::boolean[],
275 -- Months
276 ARRAY[false,false,false,false,false,false,false,false,false,false,false,false]::boolean[]
277) RETURNING jscid INTO scid;END
278$$;
279/* 3 - PGAGENT - JOB CREATION :: END */
280
281
282/* 3 - LOG ARCHIVING FUNCTIONS :: BEGIN */
283CREATE OR REPLACE FUNCTION "public"."logs_archive__insert_from_logs__olderthen_xmonths"(p_months_old INTEGER default 1)
284RETURNS SETOF BIGINT
285AS $$
286BEGIN
287
288 RETURN QUERY
289 INSERT INTO logs_archive (log_id, source_name, event_name, service_data, created_at)
290 SELECT l.log_id, l.source_name, l.event_name, l.service_data, l.created_at
291 FROM logs AS l
292 WHERE l.created_at <= NOW() - (interval '1 month' * p_months_old)
293 RETURNING log_id;
294
295END;
296$$ LANGUAGE plpgsql;
297
298
299CREATE OR REPLACE FUNCTION "public"."logs__move_to_archive__olderthen_xmonths"(p_months_old INTEGER default 1)
300RETURNS SETOF BIGINT
301AS $$
302DECLARE
303 l_inserted_ids BIGINT ARRAY := NULL;
304BEGIN
305
306 l_inserted_ids := ARRAY(SELECT * FROM "public"."logs_archive__insert_from_logs__olderthen_xmonths"(p_months_old));
307
308 RETURN QUERY
309 DELETE
310 FROM logs AS l
311 WHERE l.log_id IN
312 (
313 SELECT UNNEST(l_inserted_ids)
314 ) RETURNING log_id;
315
316END;
317$$ LANGUAGE plpgsql;
318/* 3 - LOG ARCHIVING FUNCTIONS :: END */
319
320
321/* 3 - ARCHIVE CLEANING FUNCTION :: BEGIN */
322CREATE OR REPLACE FUNCTION "public"."logs_archive__delete__olderthen_xmonths"(p_months_old INTEGER default 3)
323RETURNS SETOF BIGINT
324AS $$
325BEGIN
326
327 RETURN QUERY
328 DELETE
329 FROM logs_archive AS la
330 WHERE la.created_at <= NOW() - (interval '1 month' * p_months_old)
331 RETURNING log_id;
332
333END;
334$$ LANGUAGE plpgsql;
335/* 3 - ARCHIVE CLEANING FUNCTION :: END */
336
337
338/* 3 - INSERTING TEST DATA :: BEGIN */
339INSERT INTO logs (source_name, event_name, service_data, created_at)
340VALUES ('balance_changes', 'insert', '{ "test" : 1 }', CURRENT_TIMESTAMP - interval '2 month'),
341 ('balance_changes', 'insert', '{ "test" : 1 }', CURRENT_TIMESTAMP - interval '1 month'),
342 ('balance_changes', 'insert', '{ "test" : 1 }', CURRENT_TIMESTAMP),
343 ('balance_changes', 'insert', '{ "test" : 1 }', CURRENT_TIMESTAMP - interval '4 month'),
344 ('balance_changes', 'insert', '{ "test" : 1 }', CURRENT_TIMESTAMP - interval '11 day')
345/* 3 - INSERTING TEST DATA :: END */
346
347
348/* 3 - TESTING LOG ARCHIVING AND LOG ARCHIVE CLEANING :: BEGIN */
349SELECT * FROM "public"."logs__move_to_archive__olderthen_xmonths"(1);
350SELECT * FROM "public"."logs_archive__delete__olderthen_xmonths"(3);
351/* 3 - TESTING LOG ARCHIVING AND LOG ARCHIVE CLEANING :: END */
352
353/* 3 - CHECK FOR REMAINING DATA IN BOTH TABLES :: BEGIN */
354SELECT * FROM logs;
355
356SELECT * FROM logs_archive;
357/* 3 - CHECK FOR REMAINING DATA IN BOTH TABLES :: END */
358
359
360/*
361
362 4. There is a three-level directory structure (only the name is required, the rest is not regulated / not required, the way to build the hierarchy is at your discretion).
363 The task: create (a) table (tables), (b) functions to add a directory, (c) modify all existing fields (except ID, ofc) and
364 (d) output an ordered directory tree starting from the root or any node (ID as a function parameter).
365
366*/
367
368
369CREATE TABLE nodes
370(
371 node_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
372 path_enumeration TEXT NULL,
373 "name" TEXT NOT NULL,
374 parent_node_id BIGINT NULL,
375 node_level INT NOT NULL,
376 FOREIGN KEY (parent_node_id) REFERENCES nodes (node_id)
377);
378
379
380/*
381 I did not put file types in table.
382
383 parent_node_id - for faster search in some queries
384 (it holds the same value as last node id in path enumeration, so I did not use it as recursive relationship because it is an anti-pattern, espetially in this case),
385 node_level - for faster order by
386*/
387
388
389/* 4 - INSERTING ROOT ELEMENT :: BEGIN */
390INSERT INTO nodes (path_enumeration, parent_node_id, "name", node_level)
391VALUES (null, null, 'root', 0);
392/* 4 - INSERTING ROOT ELEMENT :: END */
393
394
395/* 4 - CREATE BEFORE TRIGGER ON INSERT AND UPDATE :: BEGIN */
396CREATE OR REPLACE FUNCTION nodes__before__insert_update()
397RETURNS TRIGGER
398AS $$
399DECLARE
400 l_path_enumeration TEXT := NULL;
401 l_node_level INT := NULL;
402BEGIN
403
404 IF NEW.parent_node_id IS NULL THEN
405 RAISE EXCEPTION 'parent_node_id can not be null';
406 END IF;
407
408 l_path_enumeration := (SELECT path_enumeration FROM nodes WHERE node_id = NEW.parent_node_id);
409 l_path_enumeration := CONCAT(COALESCE(l_path_enumeration, '/'), CONCAT(NEW.parent_node_id, '/'));
410
411 l_node_level := (SELECT length(l_path_enumeration) - length(replace(l_path_enumeration, '/', '')) / length('/')) - 1;
412
413 IF l_path_enumeration IS NULL THEN
414 RAISE EXCEPTION 'l_path_enumeration can not be null';
415 END IF;
416
417 NEW.path_enumeration = l_path_enumeration;
418 NEW.node_level = l_node_level;
419
420 RETURN NEW;
421
422END;
423$$ LANGUAGE plpgsql;
424
425
426CREATE TRIGGER nodes_audit
427BEFORE INSERT OR UPDATE ON nodes
428FOR EACH ROW EXECUTE PROCEDURE nodes__before__insert_update();
429/* 4 - CREATE BEFORE TRIGGER ON INSERT AND UPDATE :: END */
430
431
432/* 4 - INSERTING TEST DATA :: BEGIN */
433INSERT INTO nodes (parent_node_id, "name")
434VALUES (1, 'users'), (2, 'ensar'), (2, 'azer');
435/* 4 - INSERTING TEST DATA :: BEGIN */
436
437
438/* 4 - CREATE ADD NODE FUNCTION :: BEGIN */
439CREATE OR REPLACE FUNCTION "public"."nodes__add_node"(p_node_name TEXT, p_to_parent_node_id INTEGER)
440RETURNS SETOF nodes
441AS $$
442BEGIN
443
444 RETURN QUERY
445 INSERT INTO nodes (parent_node_id, "name")
446 VALUES (p_to_parent_node_id, p_node_name)
447 RETURNING *;
448
449END;
450$$ LANGUAGE plpgsql;
451/* 4 - CREATE ADD NODE FUNCTION :: END */
452
453
454/* 4 - CREATE MODIFY NODE FUNCTION :: BEGIN */
455CREATE OR REPLACE FUNCTION "public"."nodes__modify_node"(p_node_id INTEGER, p_node_name TEXT, p_to_parent_node_id INTEGER)
456RETURNS SETOF nodes
457AS $$
458BEGIN
459
460 RETURN QUERY
461 UPDATE nodes
462 SET parent_node_id = p_to_parent_node_id, "name" = p_node_name
463 WHERE node_id = p_node_id
464 RETURNING *;
465
466END;
467$$ LANGUAGE plpgsql;
468/* 4 - CREATE MODIFY NODE FUNCTION :: END */
469
470
471/* 4 - CREATE MOVE NODE FUNCTION :: BEGIN */
472CREATE OR REPLACE FUNCTION "public"."nodes__move_node"(p_node_id INTEGER, p_to_parent_node_id INTEGER)
473RETURNS SETOF nodes
474AS $$
475BEGIN
476
477 RETURN QUERY
478 UPDATE nodes
479 SET parent_node_id = p_to_parent_node_id
480 WHERE node_id = p_node_id
481 RETURNING *;
482
483END;
484$$ LANGUAGE plpgsql;
485/* 4 - CREATE MOVE NODE FUNCTION :: END */
486
487
488/* 4 - CREATE RENAME NODE FUNCTION :: BEGIN */
489CREATE OR REPLACE FUNCTION "public"."nodes__rename_node"(p_node_id INTEGER, p_node_name TEXT)
490RETURNS SETOF nodes
491AS $$
492BEGIN
493
494 RETURN QUERY
495 UPDATE nodes
496 SET "name" = p_node_name
497 WHERE node_id = p_node_id
498 RETURNING *;
499
500END;
501$$ LANGUAGE plpgsql;
502/* 4 - CREATE RENAME NODE FUNCTION :: END */
503
504
505/* 4 - INSERTING TEST DATA USING FUNCTIONS :: BEGIN */
506SELECT * FROM "public"."nodes__add_node"('ibro', 2);
507SELECT * FROM "public"."nodes__move_node"(4, 5);
508SELECT * FROM "public"."nodes__add_node"('admir', 5);
509SELECT * FROM "public"."nodes__rename_node"(5, 'denis');
510/* 4 - INSERTING TEST DATA USING FUNCTIONS :: END */
511
512
513/* 4 - CREATE SELECT NODE TREE FUNCTION :: BEGIN */
514CREATE OR REPLACE FUNCTION "public"."nodes__select__nodetree_byparentnodeid"(p_parent_node_id INTEGER)
515RETURNS SETOF nodes
516AS $$
517BEGIN
518
519 RETURN QUERY
520 SELECT *
521 FROM nodes AS n
522 WHERE n.path_enumeration LIKE '%/' || p_parent_node_id || '/%'
523 ORDER BY n.node_level ASC;
524
525END;
526$$ LANGUAGE plpgsql;
527/* 4 - CREATE SELECT NODE TREE FUNCTION :: END */
528
529
530/* 4 - CREATE SELECT CURRENT LEVEL NODES FUNCTION :: BEGIN */
531CREATE OR REPLACE FUNCTION "public"."nodes__select__currentnodelevel_byparentnodeid"(p_parent_node_id INTEGER) -- like dir command
532RETURNS SETOF nodes
533AS $$
534BEGIN
535
536 RETURN QUERY
537 SELECT *
538 FROM nodes AS n
539 WHERE n.parent_node_id = p_parent_node_id;
540
541END;
542$$ LANGUAGE plpgsql;
543/* 4 - CREATE SELECT CURRENT LEVEL NODES FUNCTION :: END */
544
545
546/* 4 - SELECTING TEST DATA USING FUNCTIONS :: BEGIN */
547SELECT * FROM "public"."nodes__select__nodetree_byparentnodeid"(2);
548SELECT * FROM "public"."nodes__select__currentnodelevel_byparentnodeid"(2);
549/* 4 - SELECTING TEST DATA USING FUNCTIONS :: END */
550
551
552/* 4 - CREATE DELETE NODE TREE FUNCTION :: BEGIN */ -- directory delete
553CREATE OR REPLACE FUNCTION "public"."nodes__delete__nodetree"(p_node_id INTEGER)
554RETURNS SETOF nodes
555AS $$
556BEGIN
557
558 RETURN QUERY
559 DELETE FROM nodes AS n
560 WHERE n.node_id IN
561 (
562 SELECT "in".node_id
563 FROM nodes AS "in"
564 WHERE "in".path_enumeration LIKE '%/' || p_node_id || '/%' OR "in".node_id = p_node_id
565 ORDER BY "in".node_level DESC
566 ) RETURNING *;
567
568END;
569$$ LANGUAGE plpgsql;
570/* 4 - CREATE DELETE NODE TREE FUNCTION :: END */
571
572
573/* 4 - DELETING TEST DATA USING FUNCTIONS :: BEGIN */
574SELECT * FROM "public"."nodes__delete__nodetree"(2);
575/* 4 - DELETING TEST DATA USING FUNCTIONS :: END */
576
577SELECT * FROM nodes
578
579
580
581
582
583
584/*
585
586 5. There is a table of balance_changes ( id bigint not null, user_id bigint not null, event_date timestamptz not null, amount numeric not null )
587 (+ funding, - withdrawal); and table of current balances ( user_id bigint not null, balance numeric not null ).
588 The task: create a query (or function), returning balances of a specified user for the last 30 days.
589
590*/
591
592
593CREATE TABLE balance_changes
594(
595 id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
596 user_id BIGINT NOT NULL,
597 event_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
598 amount NUMERIC NOT NULL
599);
600
601CREATE TABLE current_balances
602(
603 user_id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
604 balance NUMERIC NOT NULL -- I suppose that negative balance is allowed
605);
606
607
608CREATE OR REPLACE FUNCTION balance_changes__after__insert_update_delete()
609RETURNS TRIGGER
610AS $$
611BEGIN
612
613 IF (TG_OP = 'INSERT') THEN
614 UPDATE current_balances
615 SET balance = balance + NEW.amount
616 WHERE user_id = NEW.user_id;
617 ELSIF (TG_OP IN ('UPDATE', 'DELETE')) THEN -- in case of update do not check record we want to update
618 RAISE EXCEPTION '% command is not allowed', TG_OP;
619 END IF;
620
621 RETURN NEW;
622
623END;
624$$ LANGUAGE plpgsql;
625
626
627CREATE TRIGGER balance_changes_audit
628BEFORE INSERT OR UPDATE OR DELETE ON balance_changes
629FOR EACH ROW EXECUTE PROCEDURE balance_changes__after__insert_update_delete();
630
631
632
633
634/* 5 - CREATE FUNCTION :: BEGIN */
635INSERT INTO current_balances (balance)
636VALUES (0);
637
638INSERT INTO balance_changes (user_id, event_date, amount)
639VALUES (1, CURRENT_TIMESTAMP - interval '21 days', +200),
640 (1, CURRENT_TIMESTAMP - interval '17 days', -450),
641 (1, CURRENT_TIMESTAMP - interval '7 days', +300);
642/* 5 - CREATE FUNCTION :: BEGIN */
643
644
645/* 5 - CREATE FUNCTION :: BEGIN */
646CREATE OR REPLACE FUNCTION "public"."balance_changes__select__sum_last_xdays__byuserid"(p_user_id integer, p_days integer default 30)
647RETURNS TABLE("date" date, "sum" numeric)
648AS $$
649DECLARE
650 l_current_balance NUMERIC := (SELECT balance FROM current_balances WHERE user_id = p_user_id);
651 l_to_date DATE := CURRENT_DATE - (interval '1 days' * p_days);
652 l_current_date DATE := CURRENT_DATE;
653BEGIN
654
655 CREATE TEMP TABLE IF NOT EXISTS temp_balances (balance_date DATE, amount NUMERIC);
656
657 INSERT INTO temp_balances (balance_date, amount)
658 VALUES (l_current_date, l_current_balance);
659
660 WHILE l_current_date > l_to_date LOOP
661
662 l_current_balance := l_current_balance - COALESCE((SELECT SUM(amount) FROM balance_changes WHERE user_id = p_user_id AND event_date::DATE = l_current_date), 0);
663 l_current_date := l_current_date - interval '1 day';
664
665 INSERT INTO temp_balances (balance_date, amount)
666 VALUES (l_current_date, l_current_balance);
667
668 END LOOP;
669
670 RETURN QUERY
671 SELECT balance_date AS "date", amount AS "sum"
672 FROM temp_balances;
673
674 DROP TABLE IF EXISTS temp_balances;
675
676END;
677$$ LANGUAGE plpgsql
678/* 5 - CREATE FUNCTION :: END */
679
680
681/* 5 - SELECTING TEST DATA USING FUNCTIONS :: BEGIN */
682SELECT * FROM "public"."balance_changes__select__sum_last_xdays__byuserid"(1);
683
684
685INSERT INTO balance_changes (user_id, event_date, amount)
686VALUES (1, CURRENT_TIMESTAMP, +100);
687
688
689SELECT * FROM "public"."balance_changes__select__sum_last_xdays__byuserid"(1, 40);
690/* 5 - SELECTING TEST DATA USING FUNCTIONS :: END */
691
692
693
694
695/*
696
697 6. Create function-wrapper, to return the result of the previous task in json.
698
699*/
700
701
702/* 6 - CREATE FUNCTION :: BEGIN */
703CREATE OR REPLACE FUNCTION "public"."balance_changes__select__sum_last_xdays__byuserid__json_wrapper"(p_user_id integer, p_days integer default 30)
704RETURNS JSON
705AS $$
706BEGIN
707 RETURN (
708 SELECT json_agg(row_to_json(bc))
709 FROM "public"."balance_changes__select__sum_last_xdays__byuserid" (p_user_id, p_days) AS bc
710 );
711
712END;
713$$ LANGUAGE plpgsql
714/* 6 - CREATE FUNCTION :: END */
715
716
717/* 6 - SELECTING TEST DATA USING FUNCTIONS :: BEGIN */
718SELECT * FROM "public"."balance_changes__select__sum_last_xdays__byuserid__json_wrapper" (1);
719SELECT * FROM "public"."balance_changes__select__sum_last_xdays__byuserid__json_wrapper" (1, 40);
720/* 6 - SELECTING TEST DATA USING FUNCTIONS :: END */