PL/pgSQLのごく簡単な例

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ PL/pgSQLのごく簡単な例
■■■■
■■■■
■■■■ 2012/09
■■■■ 使用環境：PostgreSQL9.1.4 (CentOS6.2)
                                                                   (C) 2012 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
★自己都合によりマニュアルへのリンクは9.2としています。ご了承下さい。
★このページはそのままカット&ペーストで実行して試すことができます。


【マニュアル】

PL/pgSQL - SQL手続き言語→●[マニュアル]
CREATE FUNCTION→●[マニュアル]
DROP FUNCTION→●[マニュアル]


【参考記事】
−



■１■ 簡単なPL/pgSQL例　その１
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━


【□】 テーブルを作成

DROP TABLE t_test1;
CREATE TABLE t_test1(c1 varchar(20)); 
INSERT INTO t_test1 VALUES
('aaaaaaaa'),
('aaaaxxxx'), 
('aaaayyyy'), 
('bbbbbbbb'), 
('cccccccc'), 
('dddddddd'), 
('eeeeeeee');
 
 
 
【□】 ファンクションを作成

DROP FUNCTION fnc_test1(varchar);
CREATE OR REPLACE FUNCTION fnc_test1(p1 varchar) RETURNS int AS $$
DECLARE
  kekka int;
BEGIN
  SELECT COUNT(*) INTO kekka FROM t_test1 WHERE c1 LIKE $1 || '%';
  RETURN kekka;
END;
$$ LANGUAGE plpgsql;



【参考】古いバージョンなどでは引数の部分で名前を与えずに
        以下のようにしている場合もあり。

    create or replace function fnc_test1(varchar) returns int as $$
    declare
          p1  alias for $1;
           :
           :



【□】 実行

SELECT fnc_test1('aaa');


 fnc_test1
-----------
         3



【参考】DROP FUNCTIONでは引数部分の指定も必要

DROP FUNCTIONではFANCTIONの引数（INの引数）も与える必要がある。

ただしDROP FUNCTIONに与える引数は「データ型」の羅列だけでよい。
引数名はあってもよいが無視される。
また必要なのはINの引数のみで、OUTの引数はあってもよいが無視される。


DROP FUNCTION [ IF EXISTS ] name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
    [ CASCADE | RESTRICT ]




■２■ 簡単なPL/pgSQL例　その２ 簡単な繰り返し実行
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━


【□】 テーブルを作成

DROP TABLE t_test2;
CREATE TABLE t_test2 (c1 serial,c2 varchar(20));



【□】 ファンクションを作成

DROP FUNCTION fnc_test2;
CREATE OR REPLACE FUNCTION fnc_test2(kaisu int) RETURNS int AS $$
DECLARE
  -- kaisu int := 10;
  i int;
BEGIN
   FOR i IN 1..kaisu loop
       INSERT INTO t_test2 VALUES(default,CAST(i AS varchar));
   END LOOP;
   RETURN 0;
END;
$$ LANGUAGE plpgsql;


【□】実行

SELECT fnc_test2(5);


【□】結果の確認

SELECT * FROM t_test2;



 c1 | c2
----+----
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5






■３■ 簡単なPL/pgSQL例　その３ カーソル
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

→●[マニュアル]



【□】 テーブルを作成

DROP TABLE t_test3;
CREATE TABLE t_test3 (c1 smallint,c2 varchar(20));
--
INSERT INTO t_test3 VALUES(1,'aaaaaaaaaa');
INSERT INTO t_test3 values(2,'bbbbbbbbbb');
INSERT INTO t_test3 values(3,'bbbbbbbbbb');



【□】 ファンクションを作成

DROP FUNCTION fnc_test3() ;
CREATE OR REPLACE FUNCTION fnc_test3() RETURNS int AS $$
DECLARE
    cs CURSOR FOR SELECT * FROM t_test3;
    rec record;
    dum char;
BEGIN
    OPEN cs;
    LOOP
        FETCH cs INTO rec;
        IF NOT FOUND THEN
            EXIT;
        END IF;
        RAISE NOTICE 'record is %', rec;
    END LOOP;
    CLOSE cs;
    RETURN 0;
END;
$$ LANGUAGE plpgsql;




【□】実行

 select fnc_test3();



NOTICE:  record is (1,aaaaaaaaaa)
NOTICE:  record is (2,bbbbbbbbbb)
NOTICE:  record is (3,bbbbbbbbbb)
 fnc_test3
-----------
         0




【参考】RAISEの使い方→●[マニュアル]






以上