blob: 09fb96b9d1106824b8009550fac0a401313df553 (
plain)
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
|
-- Deploy kakeibo:assign_article_to_transaction to pg
-- requires: articles
-- requires: transactions
-- requires: appschema
BEGIN;
CREATE OR REPLACE FUNCTION kakeibo.assign_article_to_transaction(
p_id INTEGER,
p_location TEXT
) RETURNS VOID AS $$
DECLARE
v_article_id INTEGER;
BEGIN
SELECT id INTO v_article_id
FROM kakeibo.articles
WHERE location = p_location;
IF NOT FOUND THEN
RAISE EXCEPTION 'location % not found', p_location;
END IF;
UPDATE kakeibo.transactions
SET article_id = v_article_id
WHERE id = p_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'transaction id % not found', p_id;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
COMMIT;
|