diff options
| -rw-r--r-- | deploy/assign_article_to_transaction.sql | 30 | ||||
| -rw-r--r-- | revert/assign_article_to_transaction.sql | 7 | ||||
| -rw-r--r-- | sqitch.plan | 1 | ||||
| -rw-r--r-- | verify/assign_article_to_transaction.sql | 27 |
4 files changed, 65 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; diff --git a/revert/assign_article_to_transaction.sql b/revert/assign_article_to_transaction.sql new file mode 100644 index 0000000..7868609 --- /dev/null +++ b/revert/assign_article_to_transaction.sql @@ -0,0 +1,7 @@ +-- Revert kakeibo:assign_article_to_transaction from pg + +BEGIN; + +DROP FUNCTION kakeibo.assign_article_to_transaction; + +COMMIT; diff --git a/sqitch.plan b/sqitch.plan index 240155c..f4d51f9 100644 --- a/sqitch.plan +++ b/sqitch.plan @@ -31,3 +31,4 @@ articles [appschema] 2023-03-05T14:01:38Z Masaya Tojo <masaya@tojo.tokyo> # Add transactions_article_id [transactions articles] 2023-03-05T14:25:46Z Masaya Tojo <masaya@tojo.tokyo> # Add article_id column to transactions table. items_article_id [items articles] 2023-03-05T14:32:43Z Masaya Tojo <masaya@tojo.tokyo> # Add article_id column to items table. articles_update_timestamp [articles update_timestamp] 2023-03-05T17:01:58Z Masaya Tojo <masaya@tojo.tokyo> # Add update_timestamp trigger to articles table. +assign_article_to_transaction [articles transactions appschema] 2023-03-09T22:19:24Z Masaya Tojo <masaya@tojo.tokyo> # Add function to set an article to an transaction. diff --git a/verify/assign_article_to_transaction.sql b/verify/assign_article_to_transaction.sql new file mode 100644 index 0000000..7fa8ee5 --- /dev/null +++ b/verify/assign_article_to_transaction.sql @@ -0,0 +1,27 @@ +-- Verify kakeibo:assign_article_to_transaction on pg + +BEGIN; + +SELECT has_function_privilege('kakeibo.assign_article_to_transaction(INTEGER, TEXT)', 'execute'); + +DO $$ +DECLARE + v_transaction_id INTEGER; + v_article_id INTEGER; + v_location TEXT; +BEGIN + SELECT md5(random()::TEXT) INTO v_location; + SELECT kakeibo.insert_transaction(0, '2023-03-09', 'テスト') INTO v_transaction_id; + INSERT INTO kakeibo.articles (location, title) + VALUES (v_location, 'テストタイトル') RETURNING id INTO v_article_id; + PERFORM kakeibo.assign_article_to_transaction(v_transaction_id, v_location); + + PERFORM 1 from kakeibo.transactions + WHERE id = v_transaction_id + AND article_id = v_article_id; + IF NOT FOUND THEN + RAISE EXCEPTION 'Fail assign_artilce_to_transaction'; + END IF; +END $$; + +ROLLBACK; |