PostgreSQL ファンクションのサンプル(PL/pgSQL)

nas2020/12/06

PostgreSQLのPL/pgSQLのストアドファンクションのサンプルです。
(確認環境:PostgreSQL 11.2,Windows 10)

目次

ファンクション 文字列を出力するファンクション
  select文で1つの値を取得する
  select文で複数列の値を取得する
確認 ファンクションの存在確認を行う
削除 ファンクションを削除する

文字列を出力するファンクション

ファンクションは、Returnで値を返します。

CREATE OR REPLACE Function test1() RETURNS VARCHAR
AS $$
DECLARE
  str1 VARCHAR(20);
BEGIN
  str1 := 'hello world';
  RETURN str1; --hello worldと出力される
END;
$$
LANGUAGE plpgsql;

1行目は、test1というファンクションを作成します。CREATE OR REPLACEなので修正も可能です。
RETURNSの後に戻り値の型を指定します。VARCHAR型にしました。
4行目は、変数です。
7行目は、RETURNで文字列を戻します。--は単一行のコメントです。

ファンクションを実行する

上記で作成したファンクションをpsqlで実行するサンプルです。
selectとファンクション名で実行します。

select test1();

 

select文で1つの値を取得する

入力として数値を渡してsql intoで値を取得します。

CREATE OR REPLACE Function test1(id1 int) RETURNS VARCHAR
AS $$
DECLARE
  name1 VARCHAR(20);
BEGIN
  SELECT name INTO name1 FROM employee
  WHERE ID = id1;
  RETURN name1;
END;
$$
LANGUAGE plpgsql;

1行目は、引数としてintの値を受け取ります。
6行目はselect intoで取得したnameを変数のname1にセットしています。
7行目は、外部から受け取った数値で条件を指定します。

ファンクションを実行する

上記で作成したファンクションをpsqlで実行するサンプルです。
引数として1を指定しています。

select test1(1);

 

select intoで複数項目を取得する

CREATE OR REPLACE Function test1(id1 int) RETURNS VARCHAR
AS $$
DECLARE
  name1 VARCHAR(20);
  name2 VARCHAR(20);  
BEGIN
  SELECT name,romaji INTO name1,name2 FROM employee
  WHERE ID = id1;
  RETURN name1 || ',' || name2;
END;
$$
LANGUAGE plpgsql;

selectで取得する項目を複数にし、intoの後の変数も複数にすれば取得できます。

 

ファンクションの存在確認を行う

SELECT proname,proowner,pronargs,prosrc FROM pg_proc 
WHERE proname = 'test1';

pg_procテーブルに情報があります。

https://www.postgresql.jp/document/9.0/html/catalog-pg-proc.html

 

ファンクションを削除する

DROP FUNCTION test1();
DROP FUNCTION test1(int);

ファンクションに引数がある場合は、引数のデータ型を指定します。

関連の記事

PostgreSQL プロシージャのサンプル(PL/pgSQL)

△上に戻る