magic sh explain 11 Релизовать на основе одной таблицы стек для целочи

  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
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
$ ./magic.sh explain 11
Релизовать на основе одной таблицы стек для целочисленных значений,
ограниченный глубиной 10, с функциями pop, push, top, clear.
CREATE OR REPLACE FUNCTION stack_clear() RETURNS void LANGUAGE 'SQL'
AS '
DELETE FROM stack;
INSERT INTO stack VALUES(0, 0);
';
CREATE OR REPLACE FUNCTION stack_top()
RETURNS INTEGER LANGUAGE 'SQL'
AS'
SELECT val FROM stack WHERE id != 0 ORDER BY id DESC LIMIT 1;
';
CREATE OR REPLACE FUNCTION stack_push(INTEGER)
RETURNS INTEGER LANGUAGE 'SQL'
AS'
INSERT INTO stack VALUES((SELECT MAX(id) + 1 FROM stack), $1);
UPDATE stack SET val = (SELECT COUNT(id) - 1 FROM stack) WHERE id = 0; -- count without 0th record
DELETE FROM stack WHERE id > 10;
SELECT (CASE WHEN val > 10 THEN 0 ELSE 1 END) FROM stack WHERE id = 0;
';
CREATE OR REPLACE FUNCTION stack_pop()
RETURNS INTEGER LANGUAGE 'SQL'
AS'
UPDATE STACK SET val = (SELECT val FROM stack WHERE id > 0 ORDER BY id DESC LIMIT 1) WHERE id = 0;
DELETE FROM stack WHERE id IN(SELECT id FROM stack WHERE id > 0 ORDER BY id DESC LIMIT 1);
SELECT val FROM stack WHERE id = 0;
';
SELECT stack_clear();
stack_clear
-------------
SELECT stack_push(111), stack_push(222), stack_push(333);
stack_push | stack_push | stack_push
------------+------------+------------
1 | 1 | 1
SELECT * FROM stack;
id | val
----+-----
1 | 111
2 | 222
3 | 333
0 | 3
SELECT stack_top();
stack_top
-----------
333
SELECT * FROM stack;
id | val
----+-----
1 | 111
2 | 222
3 | 333
0 | 3
SELECT stack_pop();
stack_pop
-----------
333
SELECT * FROM stack;
id | val
----+-----
1 | 111
2 | 222
0 | 333
SELECT stack_pop();
stack_pop
-----------
222
SELECT stack_pop();
stack_pop
-----------
111
SELECT stack_pop();
stack_pop
-----------
SELECT * FROM stack;
id | val
----+-----
0 |
SELECT stack_clear();
stack_clear
-------------
SELECT * FROM stack;
id | val
----+-----
0 | 0