Run Code
|
Code Wall
|
Users
|
Misc
|
Feedback
|
About
|
Login
|
Theme
funkcia_transakcie
--PostgreSQL 9.5 --'\\' is a delimiter drop table ucty; CREATE TABLE ucty ( majitel_uctu TEXT, zostatok NUMERIC, ciastka NUMERIC ); INSERT INTO ucty VALUES ('Rob', 100); INSERT INTO ucty VALUES ('Maria', 200); UPDATE ucty SET zostatok = zostatok - 14.00 WHERE majitel_uctu = 'Rob'; UPDATE ucty SET zostatok = zostatok + 14.00 WHERE majitel_uctu = 'Maria'; CREATE OR REPLACE FUNCTION transakcia( i_platca TEXT, i_prijemca TEXT, -- Numeric(15,2) znamena ze max 15 cisel a 2 decimalne i_ciastka NUMERIC(15, 2)) RETURNS TEXT AS $$ DECLARE platca_zostatok NUMERIC; BEGIN SELECT zostatok INTO platca_zostatok FROM ucty WHERE majitel_uctu = i_platca FOR UPDATE; IF NOT FOUND THEN RETURN 'Ucet platcu nenajdeny'; END IF; IF platca_zostatok < i_ciastka THEN RETURN 'Nedostatok prostriedkov'; END IF; UPDATE ucty SET zostatok = zostatok + i_ciastka WHERE majitel_uctu = i_prijemca; IF NOT FOUND THEN RETURN 'Prijemca neexistuje'; END IF; UPDATE ucty SET zostatok = zostatok - i_ciastka WHERE majitel_uctu = i_platca; RETURN 'OK'; END; $$ LANGUAGE plpgsql; SELECT * FROM ucty; SELECT transakcia('Rob','Maria',14.00); SELECT * FROM ucty; SELECT * FROM transakcia('Fred','Maria',14.00); SELECT * FROM transakcia('Rob','Fred',14.00); SELECT * FROM transakcia('Rob','Maria',500.00); drop table ucty;
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
univesp
renaming JSON field
arjun
Laidata Practice
postgresql timestamp
Tememons Dashboard
Sample tables
loggin_trigger-audit
Select all countries that can be found by recursively adding all neighbouring countries (PostgreSQL)
Return IDs where there are exactly 3 occurrences1
Please log in to post a comment.