この実験 (Lab) では、PostgreSQL のストアドファンクションの開発方法を学びます。基本的なストアドファンクションの定義、デフォルト値を持つ入力パラメータの追加、クエリ内でのファンクションの実行、そして最後に、データベースを整理するために使用されていないファンクションの削除まで、一連のプロセスを体験します。
このステップでは、PostgreSQL で基本的なストアドファンクションを定義する方法を学びます。ストアドファンクションは、特定のタスクを実行する再利用可能なコードブロックであり、データベース内で実行できます。
まず、ターミナルを開き、psql コマンドラインツールを使用して PostgreSQL データベースに接続します。すべてのデータベース操作はこの psql シェル内で行います。
sudo -u postgres psqlこれで、postgres=# のような PostgreSQL プロンプトが表示されるはずです。
次に、固定の整数値を返す get_total_products という名前のファンクションを作成します。このシンプルなファンクションは、基本的な構文を理解するのに役立ちます。psql シェルで次の SQL コマンドを実行してください。
CREATE FUNCTION get_total_products()
RETURNS INTEGER AS $$
BEGIN
RETURN 100;
END;
$$ LANGUAGE plpgsql;このコマンドを分解してみましょう。
CREATE FUNCTION get_total_products(): これは、入力パラメータを持たない get_total_products という名前の新しいファンクションを定義します。RETURNS INTEGER: これは、ファンクションが INTEGER データ型の単一の値を返すことを指定します。AS $$ ... $$: ドルクォート文字列 $$ は、ファンクションの本体を囲むために使用されます。これは、ファンクションのコード内にシングルクォートが含まれる問題を回避するために PostgreSQL で一般的に行われる慣習です。BEGIN ... END;: このブロックには、ファンクションの実行可能な部分が含まれます。RETURN 100;: これはファンクションのロジックであり、単に整数 100 を返します。LANGUAGE plpgsql: これは、ファンクションが PostgreSQL の手続き型言語である plpgsql で記述されていることを指定します。コマンドを実行すると、PostgreSQL はファンクションの作成を確認します。
CREATE FUNCTIONファンクションが作成されたことを確認するには、SELECT ステートメントで呼び出すことができます。
SELECT get_total_products();出力には、ファンクションによって返された値が表示されます。
get_total_products
--------------------
100
(1 row)これにより、最初のストアドファンクションが正しく機能していることが確認できます。
ストアドファンクションは、入力パラメータを受け取ることができるようになると、より強力になります。このステップでは、2 つの数値を受け取り、それらの合計を返す新しいファンクションを作成します。
前のステップから引き続き psql シェルを使用していることを確認してください。次に、2 つの整数パラメータを受け取る add_numbers という名前のファンクションを作成します。
CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;構文は前のステップと同様ですが、ファンクションのシグネチャに重要な違いがあります。
add_numbers(a INTEGER, b INTEGER): これは、ファンクションが呼び出されたときに提供される必要がある、INTEGER 型の a と b という 2 つのパラメータを定義します。コマンドを実行すると、CREATE FUNCTION の確認が表示されます。
次に、引数として 2 つの数値を指定してファンクションをテストします。
SELECT add_numbers(15, 25);ファンクションは 2 つの数値を加算し、結果を返します。
add_numbers
-------------
40
(1 row)デフォルトのパラメータ値を持つファンクションを使用することもできます。ここでは、挨拶メッセージにデフォルト値を持つ greet_user という新しいファンクションを作成します。
CREATE OR REPLACE FUNCTION greet_user(username VARCHAR, greeting VARCHAR DEFAULT 'Hello')
RETURNS TEXT AS $$
BEGIN
RETURN greeting || ', ' || username || '!';
END;
$$ LANGUAGE plpgsql;ここでは、CREATE OR REPLACE は、ファンクションが既に存在する場合に更新します。greeting パラメータには、デフォルト値 'Hello' が割り当てられています。|| 演算子は文字列連結に使用されます。
必須の username パラメータのみを指定してファンクションをテストします。
SELECT greet_user('Alex');ファンクションはデフォルトの挨拶を使用します。
greet_user
------------------------
Hello, Alex!
(1 row)今度は、カスタムの挨拶を指定して再度呼び出します。
SELECT greet_user('Alex', 'Welcome');出力には、カスタムメッセージが表示されます。
greet_user
-----------------------
Welcome, Alex!
(1 row)ストアドファンクションの一般的な用途は、テーブルのデータに対して計算を実行することです。このステップでは、テーブルを作成し、データを投入してから、そのテーブルに対するクエリでファンクションを使用します。
まず、製品名と価格を格納するための products という名前のシンプルなテーブルを作成します。
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price NUMERIC(10, 2)
);CREATE TABLE の確認メッセージが表示されます。
次に、products テーブルにサンプルデータを挿入します。
INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.50),
('Keyboard', 75.00);INSERT 0 3 が表示され、3 行が挿入されたことを示します。
次に、売上税込み価格を計算するファンクションを作成します。このファンクションは価格を入力として受け取り、7% の税込み価格を返します。
CREATE FUNCTION calculate_taxed_price(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN price * 1.07;
END;
$$ LANGUAGE plpgsql;ファンクションを作成した後、products テーブルに対する SELECT クエリで直接使用できます。このクエリは、各製品の元の価格と税込み価格を表示します。
SELECT name, price, calculate_taxed_price(price) AS taxed_price FROM products;出力には、各行に対するファンクション呼び出しの結果が表示されます。
name | price | taxed_price
----------+---------+-------------
Laptop | 1200.00 | 1284.0000
Mouse | 25.50 | 27.2850
Keyboard | 75.00 | 80.2500
(3 rows)これにより、ビジネスロジックをファンクション内にカプセル化し、データに適用する方法を示すことができます。
不要になったデータベースオブジェクトは削除しておくことが推奨されます。このステップでは、データベースからストアドファンクションを削除（または「ドロップ」）する方法を学びます。最初のステップで作成した get_total_products ファンクションを削除します。
まず、データベース内のファンクションを一覧表示して、get_total_products が存在することを確認できます。
\dfget_total_products を含むファンクションのリストが表示されます。
ファンクションを削除するには、DROP FUNCTION コマンドを使用します。ファンクション名を指定する必要があります。ファンクションにパラメータがある場合は、その型を指定する必要がありますが、get_total_products にはパラメータがないため、名前のみを使用できます。
DROP FUNCTION get_total_products();PostgreSQL はアクションを確認します。
DROP FUNCTIONこれで、\df を使用して再度ファンクションを一覧表示すると、get_total_products がリストに表示されなくなります。
ファンクションがオーバーロードされている場合（つまり、同じ名前で異なるパラメータを持つ複数のファンクションがある場合）は、引数の型を指定することも重要です。たとえば、add_numbers ファンクションをドロップするには、その整数パラメータを指定する必要があります。
DROP FUNCTION add_numbers(INTEGER, INTEGER);最後に、環境をクリーンアップするために、前のステップで作成した products テーブルをドロップします。
DROP TABLE products;これでラボは終了です。psql シェルを終了するには、\q と入力して Enter キーを押します。
この実験では、PostgreSQL でストアドファンクションを開発する際の基本を学びました。基本的なファンクションの作成、パラメータとデフォルト値による拡張、テーブルデータに対するクエリでのファンクションの適用、そして最後にファンクションとテーブルを削除してデータベースをクリーンアップしました。これらのスキルは、よりモジュール化され効率的なデータベースソリューションを作成するために不可欠です。
