PostgreSQL初心者の悪戦苦闘ぶりをメモする pl/pgsql篇
使用可能言語の一覧表示
>createlang -U "ユーザー名" -d "データベース名" -l
Password: "パスワード"
Procedural Languages
Name | Trusted?
---------+----------
plpgsql | yes
plpgsqlのインストール
>createlang -U "ユーザー名" -d "データベース名" plpgsql
Password: "パスワード"
サンプルプログラム（sample_func.sql）
CREATE OR REPLACE FUNCTION sample_func() RETURNS timestamp AS
$$
--------------------------------------------------------
--宣言部
--------------------------------------------------------
DECLARE
current_time timestamp;
dummy varchar;
x1 integer := 100;
--カーソル定義
product_cur cursor is
select products.*
from products
order by
products.product_no
;
--カーソル変数の定義
product_rec record;
--行変数の定義
products_rowtype products%rowtype;
wn_products_no products.product_no%type;
--------------------------------------------------------
--実行部
--------------------------------------------------------
BEGIN
RAISE NOTICE 'start : [%]', clock_timestamp();
--ディレイ
select pg_sleep(3) into dummy;
--for loop
FOR i IN 1..10 LOOP
RAISE LOG 'for loop i = %',i;
END LOOP;
--cursor for loop
FOR product_rec IN select * from products order by products.product_no LOOP
RAISE LOG 'for loop products.product_no = % %',product_rec.product_no ,product_rec.name;
END LOOP;
--open cursor loop
OPEN product_cur;
LOOP
FETCH product_cur into product_rec;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE LOG 'open loop products.product_no = % %',product_rec.product_no ,product_rec.name;
END LOOP;
CLOSE product_cur;
current_time := clock_timestamp();
RAISE LOG 'global x1 = %',x1;
--サブブロックの宣言
<<sub>>
DECLARE
x1 integer := 200;
BEGIN
RAISE LOG 'local x1 = %', x1;
RAISE LOG 'local x1 = %', sub.x1;
END sub;
--サブブロックでの例外キャッチ
BEGIN
x1 := x1 / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE WARNING 'division_by_zero % %', SQLSTATE, SQLERRM;
END;
--動的SQL
wn_products_no := 1;
EXECUTE 'select products.* from products where products.product_no = ' || quote_literal(wn_products_no) INTO STRICT products_rowtype;
IF products_rowtype IS NOT NULL THEN
RAISE LOG 'FOUND product_no = % %',products_rowtype.product_no, products_rowtype.name;
ELSE
RAISE LOG 'NOT FOUND!';
END IF;
--ディレイ
select pg_sleep(2) into dummy;
RAISE NOTICE 'end : [%]', clock_timestamp();
RETURN current_time;
--------------------------------------------------------
--例外部（オプション）
--------------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
RAISE WARNING 'EXCEPTION! % %',SQLSTATE, SQLERRM;
RETURN now();
--function実行中にctrl+c等で中断させると発生
WHEN QUERY_CANCELED THEN
RAISE NOTICE 'FUNCTION CANCELD % %',SQLSTATE, SQLERRM;
RETURN now();
END;
$$
LANGUAGE plpgsql;
コンパイル
=>\i sample_func.sql
CREATE FUNCTION
実行
=>select sample_func();
sample_func
-------------------------
2008-09-04 14:42:39.078
(1 row)
OSのコマンドプロンプトからファンクションを実行
outer.sqlというファイルを作成し、以下の内容を記述して保存する。
select sample_func();
>psql -U "ユーザー名" -d "データベース名" -f c:\outer.sql
Password for user ユーザー名: "パスワード"
sample_func
-------------------------
2008-09-04 14:49:30.562
(1 row)
>
第 38章PL/pgSQL - SQL手続き言語
30.13. パスワードファイル
参考にさせて頂いたページです。
cronでSQLを実行(PostgreSql)
PostgreSQL で時間の差を取って返す
