diff options
Diffstat (limited to 'deploy/assign_article_to_transaction.sql')
-rw-r--r-- | deploy/assign_article_to_transaction.sql | 30 |
1 files changed, 30 insertions, 0 deletions
diff --git a/deploy/assign_article_to_transaction.sql b/deploy/assign_article_to_transaction.sql new file mode 100644 index 0000000..09fb96b --- /dev/null +++ b/deploy/assign_article_to_transaction.sql @@ -0,0 +1,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; |