【サンプル満載】postgresql ストアド プロシージャ自由自在(stored procedure)

select * from func_hoge('2021/10/10','大阪')
call pro_hoge('2021/10/10','大阪')

drop function  func_hoge(yymmde date,area varchar)
drop procedure pro_hoge(yymmde date,area varchar)

create table "public"."売上明細" (
  "販売日" date not null
  , "地域" character varying(50) not null
  , "品名" character varying(50) not null
  , "数量" integer
  , primary key ("販売日", "地域", "品名")
);

create table "public"."集計表" (
  "地域" character varying(50) not null
  , "数量" integer
  , primary key ("地域")
);

insert into 売上明細 values('2021/10/10','愛知','ういろう',230);
insert into 売上明細 values('2021/10/10','沖縄','紅芋タルト',80);
insert into 売上明細 values('2021/10/10','岩手','かもめの玉子',200);
insert into 売上明細 values('2021/10/10','宮城','笹かまぼこ',300);
insert into 売上明細 values('2021/10/10','京都','八つ橋',300);
insert into 売上明細 values('2021/10/10','秋田','だまこ餅',50);
insert into 売上明細 values('2021/10/10','青森','いか墨カスター',80);
insert into 売上明細 values('2021/10/10','大阪','たこやき',250);
insert into 売上明細 values('2021/10/10','長崎','長崎カステラ',100);
insert into 売上明細 values('2021/10/10','東京','東京ばなな',200);
insert into 売上明細 values('2021/10/10','奈良','柿の葉寿司',100);
insert into 売上明細 values('2021/10/10','兵庫','ゴーフル',150);
insert into 売上明細 values('2021/10/10','北海道','白い恋人',50);
insert into 売上明細 values('2021/10/11','愛知','ういろう',50);
insert into 売上明細 values('2021/10/11','沖縄','紅芋タルト',250);
insert into 売上明細 values('2021/10/11','岩手','かもめの玉子',230);
insert into 売上明細 values('2021/10/11','宮城','笹かまぼこ',200);
insert into 売上明細 values('2021/10/11','京都','八つ橋',150);
insert into 売上明細 values('2021/10/11','秋田','だまこ餅',300);
insert into 売上明細 values('2021/10/11','青森','いか墨カスター',250);
insert into 売上明細 values('2021/10/11','大阪','たこやき',300);
insert into 売上明細 values('2021/10/11','長崎','長崎カステラ',150);
insert into 売上明細 values('2021/10/11','東京','東京ばなな',250);
insert into 売上明細 values('2021/10/11','奈良','柿の葉寿司',300);
insert into 売上明細 values('2021/10/11','兵庫','ゴーフル',230);
insert into 売上明細 values('2021/10/11','北海道','白い恋人',80);
insert into 売上明細 values('2021/10/12','東京','東京ばなな',150);
insert into 売上明細 values('2021/10/13','大阪','たこやき',230);
insert into 売上明細 values('2021/10/14','京都','八つ橋',50);
insert into 売上明細 values('2021/10/15','兵庫','ゴーフル',80);
insert into 売上明細 values('2021/10/16','愛知','ういろう',50);
insert into 売上明細 values('2021/10/17','北海道','白い恋人',100);
insert into 売上明細 values('2021/10/18','青森','いか墨カスター',200);
insert into 売上明細 values('2021/10/19','秋田','だまこ餅',50);
insert into 売上明細 values('2021/10/20','長崎','長崎カステラ',250);
insert into 売上明細 values('2021/10/21','岩手','かもめの玉子',300);
insert into 売上明細 values('2021/10/22','宮城','笹かまぼこ',150);
insert into 売上明細 values('2021/10/23','沖縄','紅芋タルト',50);
insert into 売上明細 values('2021/10/24','奈良','柿の葉寿司',80);

CREATE OR REPLACE Function summary(p_area varchar)
RETURNS integer
AS $$
DECLARE
      v_suryo integer;
BEGIN
     SELECT 数量 INTO v_suryo 
     FROM 売上明細
     WHERE 地域  like  '%' || p_area || '%';
     RETURN v_suryo ;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE Function summary(p_area varchar)
RETURNS SETOF varchar
AS $$
DECLARE
     v_hinmei varchar;
     v_suryo varchar;
     v_area varchar;
BEGIN
     SELECT 品名,数量::varchar,地域
     INTO v_hinmei,v_suryo,v_area 
     FROM 売上明細
     WHERE 地域 like '%' || p_area || '%';
     RETURN QUERY VALUES (v_area),(v_suryo),(v_area);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE Function summary(p_area varchar)
RETURNS TABLE(
  v_date date,
  v_area varchar,
  v_hinmei varchar,
  v_suryo integer
)
AS $$
DECLARE
    cur cursor(c_area varchar) for
        SELECT t.販売日,t.地域,t.品名,t.数量
        FROM 売上明細 t  WHERE t.地域 like '%' || c_area || '%';
BEGIN
    FOR rec IN cur(p_area) LOOP
        v_date := rec.販売日;
        v_area := rec.地域;
        v_hinmei := rec.品名;
        v_suryo := rec.数量;
        RETURN NEXT;
    END LOOP;        
    RETURN;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE Function summary(p_area varchar)
RETURNS TABLE(
  v_date date,
  v_area varchar,
  v_hinmei varchar,
  v_suryo integer
)
AS $$
DECLARE
    cur cursor(c_area varchar) for
        SELECT t.販売日,t.地域,t.品名,t.数量
        FROM 売上明細 t  WHERE t.地域 like '%' || c_area || '%';
BEGIN
    OPEN cur(p_area);
    LOOP
        FETCH cur INTO v_date,v_area,v_hinmei,v_suryo;
            EXIT WHEN NOT FOUND;
        RETURN NEXT;
    END LOOP;
    CLOSE cur;
    RETURN;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE Function summary(p_area varchar[])
RETURNS TABLE(
   v_date date,
   v_area varchar,
   v_hinmei varchar,
   v_suryo integer
)
AS $$
DECLARE
    cur cursor(c_area varchar) for
       SELECT t.販売日,t.地域,t.品名,t.数量
       FROM 売上明細 t  WHERE t.地域 like '%' || c_area || '%';
BEGIN
    FOR n IN 1..array_length(p_area,1) LOOP
        FOR rec IN cur(p_area[n]) LOOP
           v_date := rec.販売日;
           v_area := rec.地域;
           v_hinmei := rec.品名;
           v_suryo := rec.数量;
           RETURN NEXT;
        END LOOP;        
    END LOOP;
    RETURN;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE Function summary(p_area varchar[])
RETURNS TABLE(
   v_date date,
   v_area varchar,
   v_hinmei varchar,
   v_suryo integer
)
AS $$
DECLARE
    cur cursor(c_area varchar) for
      SELECT t.販売日,t.地域,t.品名,t.数量
      FROM 売上明細 t  WHERE t.地域 like '%' || c_area || '%';
    v_area varchar;
BEGIN
    FOREACH v_area IN array p_area LOOP
        FOR rec IN cur(v_area) LOOP
           v_date := rec.販売日;
           v_area := rec.地域;
           v_hinmei := rec.品名;
           v_suryo := rec.数量;
           RETURN NEXT;
        END LOOP;        
    END LOOP;
    RETURN;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE Function summary(p_area varchar[])
RETURNS TABLE(
   v_date date,
   v_area varchar,
   v_hinmei varchar,
   v_suryo integer
)
AS $$
DECLARE
    cur cursor(c_area varchar[]) for
       SELECT t.販売日,t.地域,t.品名,t.数量
       FROM 売上明細 t  WHERE t.地域 = any(c_area);
BEGIN
        FOR rec IN cur(p_area) LOOP
            v_date := rec.販売日;
            v_area := rec.地域;
            v_hinmei := rec.品名;
            v_suryo := rec.数量;
           RETURN NEXT;
    END LOOP;
    RETURN;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE area_summary(p_area varchar)
AS $$
DECLARE
    v_area varchar;
    v_sum integer;
    cur cursor(c_area varchar) for
        SELECT t.地域,sum(t.数量)
        FROM 売上明細 t
        WHERE t.地域 = c_area
        GROUP BY t.地域;
BEGIN
    OPEN cur(p_area);
    LOOP
        FETCH cur INTO v_area,v_sum;
            EXIT WHEN NOT FOUND;
        INSERT INTO 集計表(地域,数量) values(v_area,v_sum);
    END LOOP;
    CLOSE cur;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE area_summary(p_area varchar)
AS $$
DECLARE
    v_area varchar;
    v_sum integer;
    v_sql varchar;
    cur cursor(c_area varchar) for
      SELECT t.地域,sum(t.数量)
      FROM 売上明細 t
      WHERE t.地域 = c_area
      GROUP BY t.地域;
BEGIN
    OPEN cur(p_area);
    LOOP
         FETCH cur INTO v_area,v_sum;
             EXIT WHEN NOT FOUND;
         EXECUTE 'INSERT INTO 集計表(地域,数量) values(''' || v_area || ''',' || v_sum || ')';
    END LOOP;
    CLOSE cur;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE area_summary(p_area varchar)
AS $$
declare
    v_area varchar;
    v_sum integer;
    v_sql1 varchar := 'select 地域,sum(数量) from 売上明細 where 地域 like ''%s'' group by 地域';
    v_sql2 varchar := 'INSERT INTO 集計表(地域,数量) values(''%s'',%s)';
    cur refcursor;
BEGIN
    OPEN cur  for EXECUTE format(v_sql1,p_area);
    LOOP
         FETCH cur INTO v_area,v_sum;
              EXIT WHEN NOT FOUND;
         EXECUTE format(v_sql2,v_area,v_sum);
    END LOOP;
    CLOSE cur;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION one_summary(p_area varchar,p_sum int)
 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
    BEGIN
        DELETE FROM 集計表 WHERE 地域 = p_area;
        INSERT INTO 集計表(地域,数量) values(p_area,p_sum);
    EXCEPTION
        WHEN others THEN
            RAISE;
    END;
    RETURN 0;
END;
$function$

CREATE OR REPLACE PROCEDURE area_summary(p_area varchar)
AS $$
DECLARE
    v_area varchar;
    v_sum integer;
    cur cursor(c_area varchar) for
        SELECT t.地域,sum(t.数量)
        FROM 売上明細 t
        WHERE t.地域 = c_area
        GROUP BY t.地域;
BEGIN
    BEGIN
        OPEN cur(p_area);
        LOOP
            FETCH cur INTO v_area,v_sum;
                EXIT WHEN NOT FOUND;
        
            -- トランザクション内で削除を行う
            DELETE FROM 集計表 WHERE 地域 = p_area;
            -- トランザクション内でインサートを行う           
            INSERT INTO 集計表(地域,数量) values(v_area,v_sum);
        END LOOP;
        CLOSE cur;
    EXCEPTION
            -- トランザクションが失敗した場合はロールバック
        WHEN others THEN
            -- ロールバック
            ROLLBACK;
    END;
END;
$$
LANGUAGE plpgsql;