SAK 図書館

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

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 編資料
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索



Copyright (C) 1999-2017 Y.SAK