PostgreSQL PL/Tclで関数、プロシージャを作成する

PL/Tclで関数、プロシージャを作成する

Tcl言語はほとんど使ったことがありませんでしたが、改めて、実際に使ってみると面白い言語でした。

PL/Tclを導入する

PostgreSQLの標準パッケージに同梱されていませんので使うときは別途、インストールする必要があります。

Ubuntuへのインストール

aptコマンドで、pltclがパッケージされている postgresql-pltcl-14 をインストールしようと試みたが、見つかりませんと言われインストール出来ませんでした。

$ sudo apt install postgresql-pltcl-14
パッケージリストを読み込んでいます... 完了
依存関係ツリーを作成しています... 完了        
状態情報を読み取っています... 完了        
E: パッケージ postgresql-pltcl-14 が見つかりません

Ubuntuの公式リポジトリの postgresql14関連が格納されているからpostgresql-pltcl-14_14.13-0ubuntu0.22.04.1_amd64.debをダウンロード

ダウンロードしたファイルをインストール

# sudo apt install ./postgresql-pltcl-14_14.13-0ubuntu0.22.04.1_amd64.deb 

PL/Tclの有効化範囲はデータベース単位のため、PL/Tcl を使用したいデータベースに移動後、plsql から以下のSQLコマンドを実施して、有効化します。

testdb=# CREATE EXTENSION pltcl;

Tclの基本的な文法

Tclの基本的な文法は、スペースで区切られたリスト構造をもつコマンドで構成されています。条件分岐や繰り返しなどの制御構造もコマンドとして実装されています。

基本的な書式

コマンド名 引数１ 引数２ 引数３ …

• 空白文字はコマンド名やその引数の区切り文字として扱われます。
• 全角スペースは区切り文字として扱われません。
• 改行やセミコロン「;」はコマンドの終端として扱われます。

Tcl 公式サイトではコマンドの書式を以下のような表現で記述してます。

puts ?-nonewline? ?channelID? string

「?」で囲まれた引数は省略可能です。

SQLを実行するコマンド

SQLを実行するときは、spi_exec コマンドを使用します。

書式

spi_exec ?-count n? ?-array name? command ?loop-body?

引数	説明
command	文字列として与えられたSQL問い合わせを実行します。
-count n	そのコマンドで処理する最大行数を指示します
-array name	コマンドがSELECT文の場合、その結果得られた列の値は、列名にちなんだ名前のTcl変数に格納されます。 -arrayオプションが付与された場合は、列の値は指定された名前の連想配列の要素に格納され、その配列のインデックスとして列名が使用されます。 加えて、結果内での現在の行番号（ゼロから数えます）が「.tupno」という名前の配列要素に格納されます。ただし、その名前が結果内の列名として使われていない場合に限られます。
loop-body	loop-bodyオプション引数が付与された場合、それは、問い合わせの結果内の行それぞれに対して一度だけ実行される小さなTclスクリプトです （loop-bodyはSELECT以外の問い合わせで付与された場合は無視されます）。 処理中の行の列値は、各繰り返しの前にTclの変数または配列要素に格納されます。 以下に例を示します。

PL/Tcl関数の書式

CREATE [OR REPLACE] FUNCTION 関数名 ([引数][,引数, ...]) [RETURNS 戻り値型 | RETURNS TABLE ] AS $$
  # PL/Tcl関数本体
$$ LANGUAGE pltcl

関数本体でのコメントの記述方法は以下の通り

• 「#」で始まる行はその行の改行までをコメントとして扱います。
• コメント中のセミコロンは無視されます。
• コメント行を継続させたい場合は末尾にバックスラッシュを入れる。
• コマンドの末尾にコメントを記述するには、セミコロンを用いて直前のコマンドを区切っておく必要があります。

関数 テンプレート

引数なし、戻り値がvoid型のテンプレートのテンプレート

CREATE OR REPLACE FUNCTION func_plpgsql() RETURNS void AS $$
# コメント
# PL/Tcl関数本体
$$ LANGUAGE plpgsql;

関数の作成

例1 Hello World を返すPL/Tcl関数

CREATE OR REPLACE FUNCTION HelloWorld_pltcl() RETURNS text AS $$
 return "Hello World";
$$ LANGUAGE pltcl;

例1の実施

testdb=# select HelloWorld_pltcl();
 helloworld_pltcl 
------------------
 Hello World
(1 row)

例2-1 引数を返す PL/Tcl関数

関数内で 引数は、$1, $2, と指定します

pltcl の後に STRICT を指定すると、引数の値がNULLの場合、関数は処理を行わないで、戻り値としてNULLを返してくれます。

CREATE OR REPLACE FUNCTION echo_pltcl(text) RETURNS text AS $$
# pltclの後に STRICT を指定すると、引数の値がNULLの場合、関数は処理を行わずに、戻り値としてNULLを返します
 return $1;
$$ LANGUAGE pltcl STRICT;

例2-1の実施

testdb5=# select echo_pltcl('Hello World');
 echo_pltcl  
-------------
 Hello World
(1 row)

testdb5=# select echo_pltcl(NULL) is NULL as "Is NULL";
 Is NULL 
---------
 t
(1 row)

例2-2 引数を返す PL/Tcl関数 その2

pltcl の後に STRICT を指定しないで、引数にNULLを指定したときは、引数には、長さ0 の空文字列が指定されます。そのため、以下の定義の関数で引数にNULLを指定すると、長さ0の文字列が返されます。

CREATE OR REPLACE FUNCTION echo2_pltcl(echo text) RETURNS text AS $$
 return $1;
$$ LANGUAGE pltcl;

例2-2の実施

testdb5=# select echo2_pltcl('Hello World');
 echo2_pltcl 
-------------
 Hello World
(1 row)

testdb5=# select echo2_pltcl(NULL) is NULL as "Is NULL";
 Is NULL 
---------
 f
(1 row)

testdb5=# select length(echo2_pltcl(NULL));
 length 
--------
      0
(1 row)

例2-3 引数を返す PL/Tcl関数 その3

引数がNULLかどうかを確認するには、argisnull関数を使用します。戻り値にNULLを返す場合は、return_null を実行します

CREATE OR REPLACE FUNCTION echo3_pltcl(echo text) RETURNS text AS $$
  if {[argisnull 1]} {
    return_null;
  }
  return $1;
$$ LANGUAGE pltcl;

例2-3の実施

testdb5=# select echo3_pltcl('Hello World');
 echo3_pltcl 
-------------
 Hello World
(1 row)

testdb5=# select echo3_pltcl(NULL) is NULL as "Is Null";
 Is Null 
---------
 t
(1 row)

例2-4 引数が複合型の場合

引数が複合型の場合、引数はTclの配列として、渡されます。配列の要素名は複合型の属性名です。

複合型を返す場合、列の名前/値ペアのTclのリストで返します。

CREATE TABLE public.prod (
    prod_id integer,
    prod_name text,
    price numeric
);

CREATE OR REPLACE FUNCTION echo_prod1_pltcl(prod) RETURNS prod AS $$
  return [list prod_id $1(prod_id) prod_name $1(prod_name) price $1(price)];
$$ LANGUAGE pltcl;

prod テーブルの pod_id の値を取得するときは、$1(prod_id)で取得します。

prod テーブルの行を返す場合、list prod_id $1(prod_id) prod_name $1(prod_name) price $1(price) のように列の名前、値の順番でリストを作ります

例2-4 の実施例

testdb5=# select * from echo_prod1_pltcl('(1, "かき", 100)');
 prod_id | prod_name | price 
---------+-----------+-------
       1 |  かき     |   100
(1 row)

例2-5 戻り値が複合型の集合を返す

戻り値に集合(複数の行) を返すようにする場合、RETURNS SETOF 戻り値の型 と記述します。集合を返す場合、return_next で返します。return_nextを返却する行ごとに呼び出します。

CREATE OR REPLACE FUNCTION echo_prod2_pltcl(prod) RETURNS SETOF prod AS $$
  return_next [list prod_id $1(prod_id) prod_name $1(prod_name) price $1(price)];
$$ LANGUAGE pltcl;

戻り値が集合の場合、返す行が1行の場合でも、return_nextで返します。

例2-5 の実施例

testdb5=# select * from echo_prod2_pltcl('(1, "かき", 100)');
 prod_id | prod_name | price 
---------+-----------+-------
       1 |  かき     |   100
(1 row)

例3-1 prod テーブルの prod_name の最初の行を返すSQL関数

テーブルのカラム名がTcl変数になります。

CREATE OR REPLACE FUNCTION prod_name1_pltcl() RETURNS text AS $$
 spi_exec "SELECT prod_name FROM prod"
 return $prod_name;
$$ LANGUAGE pltcl;

例3-2 prod テーブルの prod_name の最初の行を返すSQL関数

カラムに別名をつけた場合、別名がTcl変数になります

CREATE OR REPLACE FUNCTION prod_name2_pltcl() RETURNS text AS $$
 spi_exec "SELECT prod_name As name FROM prod"
 return $name;
$$ LANGUAGE pltcl;

例3-2の実施

testdb=# CREATE OR REPLACE FUNCTION prod_name_pltcl() RETURNS text AS $$
 spi_exec "SELECT prod_name As name FROM prod"
 return $name;
$$ LANGUAGE pltcl;
CREATE FUNCTION
testdb=# select prod_name_pltcl();
 prod_name_pltcl 
-----------------
 みかん
(1 row)

例4-1 戻り値が集合の場合の定義

loop-bodyオプション引数 を使い、loop-body 内で return_next で値を返します

CREATE OR REPLACE FUNCTION prod_name_list1_pltcl() RETURNS TABLE(prod_name text) AS $$
  spi_exec "SELECT prod_name FROM prod" {
     return_next $prod_name 
  }
$$ LANGUAGE pltcl;

例4-1 の実施

testdb5=# select * from prod_name_list1_pltcl();
 prod_name 
-----------
 みかん
 りんご
 メロン
(3 rows)

例4-2 戻り値が集合の場合の定義 その2

カラムに別名をつけた場合、別名がTcl変数になります

CREATE OR REPLACE FUNCTION prod_name_list2_pltcl() RETURNS SETOF text AS $$
  spi_exec "SELECT prod_name as name FROM prod" {
     return_next $name 
  }
$$ LANGUAGE pltcl;

例4-2 の実施

testdb5=# select * from prod_name_list2_pltcl();
 prod_name_list2_pltcl 
-----------------------
 みかん
 りんご
 メロン
(3 rows)

例4-3 戻り値が複合型の集合の場合の定義

テーブルの行を返す場合、list prod_id $prod_id prod_name $prod_name price $price のように列の名前、値の順番でリストを作ります

CREATE OR REPLACE FUNCTION prod_list_pltcl() RETURNS SETOF prod AS $$
  spi_exec "SELECT prod_id, prod_name, price FROM prod" {
     return_next [list prod_id $prod_id prod_name $prod_name price $price]; 
  }
$$ LANGUAGE pltcl;

例4-3の実施

testdb5=# select * from prod_list_pltcl();
 prod_id | prod_name | price 
---------+-----------+-------
       1 | みかん    |    50
       2 | りんご    |    70
       3 | メロン    |   100
(3 rows)

例4-4 戻り値が複合型の集合の場合の定義 その2

CREATE OR REPLACE FUNCTION prod_list2_pltcl() RETURNS SETOF prod AS $$
  spi_exec "SELECT * FROM prod" {
     return_next [list prod_id $prod_id prod_name $prod_name price $price]; 
  }
$$ LANGUAGE pltcl;

例4-4 の実施

testdb5=# select * from prod_list2_pltcl();
 prod_id | prod_name | price 
---------+-----------+-------
       1 | みかん    |    50
       2 | りんご    |    70
       3 | メロン    |   100
(3 rows)

例5 戻り値がvoid型

CREATE OR REPLACE FUNCTION testdataProd_pltcl() RETURNS void AS $$
 spi_exec "INSERT INTO prod VALUES(1, 'みかん', 10)"
$$ LANGUAGE pltcl;

例5の実施

testdb5=# select * from prod;
 prod_id | prod_name | price 
---------+-----------+-------
       1 | みかん    |    50
       2 | りんご    |    70
       3 | メロン    |   100
(3 rows)

testdb5=# select * from testdataProd_pltcl();
 testdataprod_pltcl 
--------------------
 
(1 row)

testdb5=# select * from prod;
 prod_id | prod_name | price 
---------+-----------+-------
       1 | みかん    |    50
       2 | りんご    |    70
       3 | メロン    |   100
       1 | みかん    |    10
(4 rows)

例6 戻り値の型をvoid以外でreturn 文を入れていなくてもエラーになりません。

CREATE OR REPLACE FUNCTION testdataProd2_pltcl() RETURNS text AS $$
  spi_exec "INSERT INTO prod VALUES(1, 'みかん', 10)"
$$ LANGUAGE pltcl;

例6の実施

testdb5=# CREATE OR REPLACE FUNCTION testdataProd2_pltcl() RETURNS text AS $$
  spi_exec "INSERT INTO prod VALUES(1, 'みかん', 10)"
$$ LANGUAGE pltcl;
CREATE FUNCTION
testdb5=# select * from testdataProd2_pltcl();
 testdataprod2_pltcl 
---------------------
 1
(1 row)

例6の参考 spi_exec の戻り値を返す

CREATE OR REPLACE FUNCTION testdataProd3_pltcl() RETURNS text AS $$
  set a [spi_exec "INSERT INTO prod VALUES(1, 'みかん', 10)"]
  return $a
$$ LANGUAGE pltcl;

例6の参考の実施

testdb5=# select * from testdataProd3_pltcl();
 testdataprod3_pltcl 
---------------------
 1
(1 row)

例7 RETURNING句付きのInsert文でSQL関数を作成

戻り値がTABLEのときは、返すデータの件数が1件の場合でも、return_next で 結果を返します

CREATE OR REPLACE FUNCTION testdataProd4_pltcl() RETURNS TABLE(prod_id integer, prod_name text, price numeric) AS  $$
 spi_exec "INSERT INTO prod VALUES(1, 'みかん', 10) RETURNING prod_id, prod_name, price"
 return_next [list prod_id $prod_id prod_name $prod_name price $price]
$$ LANGUAGE pltcl;

例7の実施

testdb5=# select * from testdataProd4_pltcl();
 prod_id | prod_name | price 
---------+-----------+-------
       1 | みかん    |    10
(1 row)

PL/Tclでプロシージャの作成

PL/Tclプロシージャの書式

CREATE [OR REPLACE] PROCEDURE プロシージャ名 ([引数][,引数, ...])  LANGUAGE pltcl AS $$
# PL/Tclプロシージャ本体
$$ 

PL/Tclプロシージャ テンプレート

引数なしのテンプレート

CREATE OR REPLACE PROCEDURE proc()
LANGUAGE pltcl
AS $$
# PL/Tclプロシージャ本体
$$;

プロシージャの作成

例1 引数はSQL文内でも$1, $2,.. と指定します

CREATE OR REPLACE PROCEDURE insertProd_pltcl(prod_name text, price numeric)
LANGUAGE pltcl
AS $$
    spi_exec "INSERT INTO prod VALUES(nextval('seqno'), '$1', $2)"
$$;

例1の実施

testdb5=# call insertProd_pltcl('か''き', 100);
CALL
testdb5=# call insertProd_pltcl('か''き', 100);
ERROR:  syntax error at or near "き"
CONTEXT:  syntax error at or near "き"
    while executing
"spi_exec "INSERT INTO prod VALUES(nextval('seqno'), '$1', $2)""
    (procedure "__PLTcl_proc_18356" line 4)
    invoked from within
"__PLTcl_proc_18356 か'き 100"
in PL/Tcl function "insertprod_pltcl"

引数の値の一部に単一引用符やバックスラッシュ文字がある場合は、エラーになってしまいます。

例2

引数の値の一部に単一引用符やバックスラッシュ文字がある場合、２重化しないといけないので、quote コマンドを使用して、２重化させます

CREATE OR REPLACE PROCEDURE insertProd_pltcl(prod_name text, price numeric)
LANGUAGE pltcl
AS $$
    spi_exec "INSERT INTO prod VALUES(nextval('seqno'), '[quote $1]', $2)"
$$;

例2の実施

testdb5=# call insertProd_pltcl('か''き', 100);
CALL

参照情報

• PL/Tcl — Tcl手続き言語
• Tcl Developer Xchange
• Tcl - Wikipedia
• プログラミング言語 Tcl/Tkの紹介