SAK 図書館
SAK 図書館
PostgreSQL 編19 - ストアドファンクション、function、PL/pgSQL、トリガー
■ストアドファンクション (ストアドプロシジャ)
・SQL の組み込み関数以外にユーザが独自の関数を作ることができる。
language 'sql' では、単純なものしか作成できない。
Oracle の PL/SQL と同等のものは、以下で説明する PL/pgSQL で作成する。
drop function gettest1(char);
create function gettest1(char) returns int8 as
'select count(*) from testm where key = $1'
language 'sql'
;
select gettest1('a001');
gettest1
----------
1
drop function gettest1(char);
create function gettest1(char) returns int8 as
'select count(*) from testm where key like $1 || ''%'''
language 'sql'
;
select gettest1('a');
gettest1
----------
2
■PL/pgSQL
・language 'plpgsql' では、PL/pgSQL の制御構造を持ったストアドファンク
ションが作成できる。構文は Pascal 言語に似ている。
・PL/SQL とは違い PL/pgSQL 内ではトランザクションコマンドは使えない。
しかし、ADO - ODBC や JDBC でのトランザクション配下から、呼ばれた
ストアドやトリガーは、ちゃんとロールバック可能である。
実用システムとしては、まったく問題ないと考える。
・標準インストール状態では、PL/pgSQL は使用できないようである。
create function の language 'plpgsql' で、次のエラーが出るときは、
createlang されていない可能性がある。
Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
Pre-installed languages are SQL, C, and internal.
Additional languages may be installed using 'createlang'.
・PL/pgSQL が使用可能かは、次のように調べる。
select * from pg_language;
lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
----------+---------+--------------+---------------+-------------
internal | f | f | 0 | n/a
C | f | f | 0 | /bin/cc
sql | f | f | 0 | postgres
・plpgsql がなければ、Cygwin のコマンドで、次のように入力する。
エラーなしで終了すれば、OK である。
createlang -h 127.0.0.1 -d sak -U administrator plpgsql
| | |_ 言語 plpgsql
| |_ 管理者ユーザ
|_ データベース
・再度 psql で、インストールされたか確認する。
select * from pg_language;
lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
----------+---------+--------------+---------------+-------------
internal | f | f | 0 | n/a
C | f | f | 0 | /bin/cc
sql | f | f | 0 | postgres
plpgsql | t | t | 16577 |
・これで、PL/pgSQL が作成できる。
drop function gettest1(char);
create function gettest1(char) returns int8 as
'
declare
p1 alias for $1;
ret int8;
begin
select count(*) into ret from testm where key = p1;
return ret;
end;
'
language 'plpgsql'
;
select gettest1('a001');
gettest1
----------
1
drop function gettest1(char);
create function gettest1(char) returns int8 as
'
declare
p1 alias for $1;
ret int8;
begin
select count(*) into ret from testm where key like p1 || ''%'';
return ret;
end;
'
language 'plpgsql'
;
select gettest1('a');
gettest1
----------
2
■データベーストリガー
・実行のタイミングは、before、after から選べる。
PostgreSQL のトリガは、trigger から function を呼び出す形になる。
new、old は、新と旧のデータを示す。
tg_op は、'INSERT'、'UPDATE'、'DELETE' を示す。
drop function trifunc1();
create function trifunc1() returns opaque as
'
declare
s varchar(20);
begin
if tg_op = ''INSERT'' then
s := ''triins'';
insert into test2m values(new.key, s);
else
if tg_op = ''UPDATE'' then
s := ''triups'';
insert into test2m values(new.key, s);
else
s := ''tridlt'';
insert into test2m values(new.key, s);
end if;
end if;
return new;
end;
'
language 'plpgsql'
;
drop trigger tritest1 on testm;
create trigger tritest1
before insert or update or delete on testm for each row
execute procedure trifunc1()
;
select * from testm;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 1 | 2 | 3
a011 | 1 | 2 | 3
b002 | 10 | 20 | 30
c003 | 100 | 200 | 300
select * from test2m;
key | code1
-----+-------
insert into testm values ('t001', 11, 22, 33);
select * from testm;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 1 | 2 | 3
a011 | 1 | 2 | 3
b002 | 10 | 20 | 30
c003 | 100 | 200 | 300
t001 | 11 | 22 | 33
select * from test2m;
key | code1
----------+----------
t001 | triins
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
■SQL 基礎編資料
■SQL 基礎実地編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
Copyright (C) 1999-2018 Y.SAK