deploy/insert_item.sql


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

-- Deploy kakeibo:insert_item to pg
-- requires: schema
-- requires: items

BEGIN;

CREATE OR REPLACE FUNCTION kakeibo.insert_item(
  p_transaction_id INTEGER,
  p_category TEXT,
  p_subcategory TEXT,
  p_amount INTEGER,
  p_note TEXT
) RETURNS INTEGER AS $$
  DECLARE
    inserted_id INTEGER;
  BEGIN
    INSERT INTO kakeibo.items (transaction_id, category, subcategory, amount, note)
    VALUES (p_transaction_id, p_category, p_subcategory, p_amount, p_note)
           RETURNING id INTO inserted_id;

    RETURN inserted_id;
  END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMIT;