-- 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;
