PLV8は DO による無名コードブロック実行にも対応しているため、手始めに DO ブロックで試してみます。
素朴なSQL実行の例
do $$ //SQLを実行して結果を取得 const inactives = plv8.execute(` selectcount(1)::integeras inactive_count from customer c where c.active =0 `) plv8.elog(NOTICE, `There is ${inactives[0].inactive_count} inactive members.`) $$ language plv8;
実行結果は以下です。特に問題なく期待通りの結果が得られました。
pagila=> do $$ pagila$> // SQLを実行して結果を取得 pagila$> const inactives = plv8.execute(` pagila$> select count(1)::integer as inactive_count pagila$> from customer c pagila$> where c.active = 0 pagila$> `) pagila$> plv8.elog(NOTICE, `There is ${inactives[0].inactive_count} inactive members.`) pagila$> $$ language plv8; NOTICE: There is 15 inactive members. DO
do $$ let plan, cursor try { // プリペアド文を作成 plan = plv8.prepare(` select c.customer_id, c.first_name from customer c whereexists ( select1 from rental r where c.customer_id = r.customer_id groupby r.customer_id havingcount(1) > $1 ) orderby c.customer_id `, ['integer'])
// プリペアド文を実行してカーソルを取得 cursor= plan.cursor([40]) let customer // カーソルを使ってループ while (customer = cursor.fetch()) { plv8.elog(NOTICE, `id: ${customer.customer_id}, name: ${customer.first_name}`) } } finally { // カーソルとプリペアド文は解放が必要 cursor?.close() plan?.free() } $$ language plv8;
実行結果は以下です。こちらも特に問題なく期待通りの結果が得られました。
実行結果
pagila=> do $$ pagila$> let plan, cursor pagila$> try { pagila$> // プリペアド文を作成 pagila$> plan = plv8.prepare(` pagila$> select c.customer_id, c.first_name pagila$> from customer c pagila$> where exists ( pagila$> select 1 pagila$> from rental r pagila$> where c.customer_id = r.customer_id pagila$> group by r.customer_id pagila$> having count(1) > $1 pagila$> ) pagila$> order by c.customer_id pagila$> `, ['integer']) pagila$> pagila$> // プリペアド文を実行してカーソルを取得 pagila$> cursor = plan.cursor([40]) pagila$> let customer pagila$> // カーソルを使ってループ pagila$> while (customer = cursor.fetch()) { pagila$> plv8.elog(NOTICE, `id: ${customer.customer_id}, name: ${customer.first_name}`) pagila$> } pagila$> } finally { pagila$> // カーソルとプリペアド文は解放が必要 pagila$> cursor?.close() pagila$> plan?.free() pagila$> } pagila$> $$ language plv8; NOTICE: id: 75, name: TAMMY NOTICE: id: 144, name: CLARA NOTICE: id: 148, name: ELEANOR NOTICE: id: 236, name: MARCIA NOTICE: id: 526, name: KARL DO
関数として作成して実行
CREATE FUNCTION 文を手作成
カーソルで取得する例のSQLを関数にして実行してみます。DO ブロックの代わりに CREATE FUNCTION にするだけなので、ついでに閾値を関数の引数で渡すように変更してみます。
createfunction print_top_customers(frequency integer) returns void as $$ -- ---------- 処理はほぼ同じなため記載省略 ---------- $$ language plv8 stable;
実行結果
pagila=> create function print_top_customers(frequency integer) returns void as $$ pagila$> let plan, cursor pagila$> try { pagila$> // プリペアド文を作成 pagila$> plan = plv8.prepare(` pagila$> select c.customer_id, c.first_name pagila$> from customer c pagila$> where exists ( pagila$> select 1 pagila$> from rental r pagila$> where c.customer_id = r.customer_id pagila$> group by r.customer_id pagila$> having count(1) > $1 pagila$> ) pagila$> order by c.customer_id pagila$> `, ['integer']) pagila$> pagila$> // プリペアド文を実行してカーソルを取得 pagila$> cursor = plan.cursor([frequency]) pagila$> let customer pagila$> // カーソルを使ってループ pagila$> while (customer = cursor.fetch()) { pagila$> plv8.elog(NOTICE, `id: ${customer.customer_id}, name: ${customer.first_name}`) pagila$> } pagila$> } finally { pagila$> // カーソルとプリペアド文は解放が必要 pagila$> cursor?.close() pagila$> plan?.free() pagila$> } pagila$> $$ language plv8 stable; CREATE FUNCTION pagila=> select print_top_customers(40); NOTICE: id: 75, name: TAMMY NOTICE: id: 144, name: CLARA NOTICE: id: 148, name: ELEANOR NOTICE: id: 236, name: MARCIA NOTICE: id: 526, name: KARL print_top_customers ---------------------
(1 行)
DO ブロックでの実行と同じ結果が得られました。
JavaScript実装をバンドルして CREATE FUNCTION SQL を生成
前の例では CREATE FUNCTION 文を手作成しました。そのSQLにおいて1行目と最終行以外はJSの実装です。であれば、JS部分は独立して開発して最後に CREATE FUNCTION 文を生成できれば色々捗りそうです。それをやってくれるツールであるPLV8ifyが公式ドキュメントで紹介されていますので、ここからはPLV8ifyを使った関数の開発を試してみます。
なお、 plv8ify は実際には上記でインストールされたバージョンそのものではなく、バグと思われる挙動や利便性向上をローカルで修正したものを使用しました。このため、ここより後の例ではTypeScriptで実装した関数に対して生成される CREATE FUNCTION 文の関数定義では関数名と引数名が snake_case 化されるようにしています。
PLV8関数のTS実装例
前の手作成 CREATE FUNCTION の例をTS実装にし、ついでに検索結果をログではなく戻り値として返却するように変更したものがこちらです。
fetch_top_customers.ts
/** * @plv8ify_param {integer} frequency * @plv8ify_return {setof record} * @plv8ify_volatility STABLE */ exportfunctionfetchTopCustomers(frequency: number): void { let plan!: PreparedPlan, cursor!: Cursor; try { plan = plv8.prepare(` select c.customer_id, c.first_name from customer c where exists ( select 1 from rental r where c.customer_id = r.customer_id group by r.customer_id having count(1) > $1 ) order by c.customer_id `, ['integer']);
pagila=> \i plv8ify-dist/fetch_top_customers.plv8.sql psql:plv8ify-dist/fetch_top_customers.plv8.sql:1: NOTICE: function fetch_top_customers(pg_catalog.int4) does not exist, skipping DROP FUNCTION CREATE FUNCTION pagila=> select * from fetch_top_customers(40) as (idinteger, name varchar); id | name -----+--------- 75 | TAMMY 144 | CLARA 148 | ELEANOR 236 | MARCIA 526 | KARL (5 行)
CREATEFUNCTION public.rewards_report(min_monthly_purchases integer, min_dollar_amount_purchased numeric, today date) RETURNS SETOF public.customer LANGUAGE plpgsql SECURITY DEFINER AS $_$ DECLARE last_month_start DATE; last_month_end DATE; rr RECORD; tmpSQL TEXT; BEGIN
/* Some sanity checks... */ IF min_monthly_purchases =0THEN RAISE EXCEPTION 'Minimum monthly purchases parameter must be > 0'; END IF; IF min_dollar_amount_purchased =0.00THEN RAISE EXCEPTION 'Minimum monthly dollar amount purchased parameter must be > $0.00'; END IF;
/* Create a temporary storage area for Customer IDs. */ CREATE TEMPORARY TABLE tmpCustomer (customer_id INTEGERNOTNULLPRIMARY KEY);
/* Find all customers meeting the monthly purchase requirements */
tmpSQL :='INSERT INTO tmpCustomer (customer_id) SELECT p.customer_id FROM payment AS p WHERE DATE(p.payment_date) BETWEEN '||quote_literal(last_month_start) ||' AND '|| quote_literal(last_month_end) ||' GROUP BY customer_id HAVING SUM(p.amount) > '|| min_dollar_amount_purchased ||' AND COUNT(customer_id) > '||min_monthly_purchases ;
EXECUTE tmpSQL;
/* Output ALL customer information of matching rewardees. Customize output as needed. */ FOR rr INEXECUTE'SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id' LOOP RETURN NEXT rr; END LOOP;
/* Clean up */ tmpSQL :='DROP TABLE tmpCustomer'; EXECUTE tmpSQL;
RETURN; END $_$;
上記PL/pgSQL関数を以下のようにTS実装しました。
引数追加版 rewards_report のTS実装
v8_rewards_report.ts
/** * @plv8ify_param {integer} minMonthlyPurchases * @plv8ify_param {numeric} minDollarAmountPurchased * @plv8ify_param {date} today * @plv8ify_return {setof public.customer} * @plv8ify_volatility VOLATILE */ exportfunctionv8RewardsReport( minMonthlyPurchases: number, minDollarAmountPurchased: number, today: Date, ): void { // Some sanity checks... if (minMonthlyPurchases === 0) { thrownewError('Minimum monthly purchases parameter must be > 0'); } if (minDollarAmountPurchased === 0.00) { thrownewError('Minimum monthly dollar amount purchased parameter must be > $0.00'); }
const lastMonthStart = plv8.execute(` SELECT DATE_TRUNC('month', $1::timestamp - '3 month'::interval) AS val `, [today])[0].val; const lastMonthEnd = plv8.execute(` SELECT LAST_DAY($1) AS val `, [lastMonthStart])[0].val;
// Create a temporary storage area for Customer IDs. plv8.execute(` CREATE TEMPORARY TABLE tmpCustomer (customer_id INTEGER NOT NULL PRIMARY KEY) `);
// Find all customers meeting the monthly purchase requirements plv8.execute(` INSERT INTO tmpCustomer (customer_id) SELECT p.customer_id FROM payment AS p WHERE DATE(p.payment_date) BETWEEN $1 AND $2 GROUP BY customer_id HAVING SUM(p.amount) > ${minDollarAmountPurchased} AND COUNT(customer_id) > ${minMonthlyPurchases} `, [lastMonthStart, lastMonthEnd]);
// Output ALL customer information of matching rewardees. // Customize output as needed. let plan!: PreparedPlan, cursor!: Cursor; try { plan = plv8.prepare(` SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id `); cursor = plan.cursor(); letcustomer: SQLRow; while ((customer = cursor.fetch())) { plv8.return_next(customer); } } finally { cursor?.close(); plan?.free(); }
// Clean up plv8.execute('DROP TABLE tmpCustomer'); }
前の例と同様に CREATE FUNCTION SQL を生成し関数を作成してSQLを実行しました。
pagila=> select customer_id, first_name pagila-> from v8_rewards_report(10, 40, '2022-06-22'); customer_id | first_name -------------+------------ 147 | JOANNE 158 | VERONICA 179 | DANA 366 | BRANDON 381 | BOBBY 410 | CURTIS 416 | JEFFERY 526 | KARL (8 行)
pagila=> select customer_id, first_name pagila-> from rewards_report(10, 40, '2022-06-22'); customer_id | first_name -------------+------------ 147 | JOANNE 158 | VERONICA 179 | DANA 366 | BRANDON 381 | BOBBY 410 | CURTIS 416 | JEFFERY 526 | KARL (8 行)
/** * @plv8ify_param {timestamp with time zone} date * @plv8ify_return {date} */ exportfunctionv8LastDay2( date: Date, ): Date { returndayjs(date).endOf('month').toDate(); }
このTS実装から生成した CREATE FUNCTION SQL を確認すると、インポートしている dayjs はインライン化されることが分かります。
/** * @plv8ify_param {integer} inventoryId */ exportfunctionv8InventoryInStock( inventoryId: number, ): boolean { // AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE // FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
constrentals: number = plv8.execute(` SELECT count(*) AS val FROM rental WHERE inventory_id = ${inventoryId} `)[0].val;
if (rentals === 0) { returntrue; }
constout: number = plv8.execute(` SELECT COUNT(rental_id) AS val FROM inventory LEFT JOIN rental USING(inventory_id) WHERE inventory.inventory_id = ${inventoryId} AND rental.return_date IS NULL `)[0].val;
return out <= 0; }
CREATE FUNCTION SQL を生成し関数を作成してSQLを実行すると、PLV8版とPL/pgSQL版で同じ結果が得られます。
実行結果
pagila=> select pagila-> iid, pagila-> inventory_in_stock(iid) as in_stock, pagila-> v8_inventory_in_stock(iid) as in_stock_v8 pagila-> from generate_series(1, 10) as iid; iid | in_stock | in_stock_v8 -----+----------+------------- 1 | t | t 2 | t | t 3 | t | t 4 | t | t 5 | t | t 6 | f | f 7 | t | t 8 | t | t 9 | f | f 10 | t | t (10 行)
const inventoryId = 1; expect(v8InventoryInStock(inventoryId)).toBe(true); expect(plv8.execute) .toHaveBeenNthCalledWith(1, ` SELECT count(*) AS val FROM rental WHERE inventory_id = ${inventoryId} `) .toHaveBeenNthCalledWith(2, ` SELECT COUNT(rental_id) AS val FROM inventory LEFT JOIN rental USING(inventory_id) WHERE inventory.inventory_id = ${inventoryId} AND rental.return_date IS NULL `); });
