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
26

-- Deploy kakeibo:insert_item to pg
-- requires: appschema
-- 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;