$ ./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