PostgreSQL【PL/pgSql】

PL/pgSQL
すべてのSQL文は、データベース・サーバによって独立して実行される必要があります.これは、クライアント・アプリケーションが各コマンドをデータベース・サーバに送信し、このコマンドを処理し、結果を受信し、演算を行い、サーバに別のコマンドを送信する必要があることを意味します.これらのものはすべてプロセス間の通信を生み出し、クライアントが別のマシンにいる場合、ネットワークのオーバーヘッドを引き起こすこともあります.
PL/pgSQLを使用すると、1つの演算と一連のコマンドをデータベース・サーバ内で1つのブロックを構成することができます.これにより、プロセス言語の力があり、SQLの使用を簡素化することができます.そのため、クライアント/サービス通信の過熱を払う必要がないため、多くの時間を節約できます.これにより、パフォーマンスが大幅に向上する可能性があります.
PL/pgSQL引用符
PL/pgSQL関数のコードはすべてCREATE FUNCTIONの中で1つの文字列のテキストの方式で宣言したのです.単一引用符を両側に囲む通常の方法で文字列テキストを書く場合は、任意の関数内の単一引用符を2部書かなければなりません.似たようなのは反スラッシュでも二重にしなければなりません.2つの引用符は非常に退屈で、より複雑な場合、コードは理解しにくいかもしれません.自分が半分以上の引用符を必要としていることに気づきやすいからです.「ドル文字で囲む」という文字列で関数体を書くことをお勧めします.ドル記号で囲む場合は、引用符を2つ書く必要はありません.各層の引用符で囲むネストのために異なるドル記号で囲むセパレータを選択するだけです.例えば、CREATE FUNCTIONコマンドを書くかもしれません.
宣言

name[ CONSTANT ]type[ NOT NULL ] [ { DEFAULT | := }expression];

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.fieldname%TYPE;
arow RECORD;

関数別名
関数に渡されるパラメータは、$1,$2などの識別子で表されます.可読性を向上させるために、$nパラメータ名に別名を宣言できます.

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

同以下:

CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

コピーの種類:

user_id users.user_id%TYPE;

行のタイプ:

nametable_name%ROWTYPE; namecomposite_type_name;

複合型変数を
行変数(または
row-type変数).このような変数は、コマンドのフィールドセットが変数宣言のタイプに一致する限り、SELECTまたはFORコマンドの結果の完全な行を保存することができる.ローの数値の独立したフィールドは、rowvarなどの一般的なポイント表現を使用してアクセスする.field.

CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;

記録タイプ:
式:
結果のない式またはコマンドを実行します.
式またはコマンドを計算しますが、結果は破棄されます(通常、有用な副作用がありますが、有用な結果値が存在しない関数を呼び出すことが多いためです).PL/pgSQLでこのことをするには、PERFORM文を使用します.

PERFORM query;

動的コマンドを実行するたびに、異なるテーブルまたは異なるデータ型のコマンドに関連する.
ダイナミックコマンドを使用する場合は、単一引用符を逃れる必要があります.あなたの関数内の固定テキストを囲む方法はドルで囲むことをお勧めします.構築されたクエリーに挿入する動的数値にも、引用符文字が含まれている可能性があるため、特別な処理が必要です.

EXECUTE 'UPDATE tbl SET '
        || quote_ident(columnname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE ...';

セキュリティのために、フィールドとテーブル識別子を含む変数を関数quote_に渡すident. 数値を含む変数は、構築された状態のコマンド文字列の外にテキスト文字列がある場合はquote_に渡されます.literal. 両方とも、入力テキストを単一または二重引用符で囲み、埋め込まれた特殊な文字に対して適切な脱出処理を行います.
結果ステータスの取得
第一の方法はGET DIAGNOSTICSを使用することであり、その形式は以下の通りである.

GET DIAGNOSTICSvariable=item[ , ... ];

コマンド効果を判断するもう1つの方法は、boolean型の特殊変数FOUNDです.FOUNDはPL/pgSQL関数ごとに偽になります.次の文で設定されます.

1つのSELECT INTO文1行を返すとFOUNDが真、返さないと偽に設定されます.

1つのPERFORM文が1行生成(または破棄)された場合はFOUNDを真とし、生成されなかった場合は偽とする.

少なくとも1行に影響がある場合、UPDATE、INSERT、DELETE文はFOUNDを真とし、行がない場合は偽とする.

FETCH文戻り行の場合はFOUNDを真とし、戻り行の場合は偽とする.

FOR文を1回または複数回反復した場合、FOUNDを真とし、そうでなければ偽とする.この法則はすべてのFOR文の3つのバリエーション(整数FORループ,レコードセットのFORループ,

せいぎょこうぞう
関数から戻る

RETURN expression;

式付きRETURNは、関数を終了するために使用され、expressionの値は呼び出し元に返されます.

RETURN NEXT expression;

条件
IF文は、ある条件に従ってコマンドを実行することができます.PL/pgSQLには5つの形式のIFがあります.

IF ... THEN

IF ... THEN ... ELSE

IF ... THEN ... ELSE IF

IF ... THEN ... ELSIF ... THEN ... ELSE

IF ... THEN ... ELSEIF ... THEN ... ELSE

IF boolean-expression THEN     statements [ ELSIF boolean-expression THEN     statements [ ELSIF boolean-expression THEN     statements...]]
[ ELSE statements]
END IF;

単純サイクル

[ <<label>> ]
LOOP     statements END LOOP [ label];

LOOP
    --     
    IF count > 0 THEN
        EXIT;  -- exit loop
    END IF;
END LOOP;

LOOP
    --     
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    --     count     [50 .. 100]        
END LOOP;

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    --          
END LOOP;

FOR i IN 1..10 LOOP
  --           
    RAISE NOTICE 'i IS %', i;
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    --           
END LOOP;

CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
     mviews RECORD;

BEGIN
     PERFORM cs_log('Refreshing materialized views...');

     FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

         --    "mviews"         cs_materialized_views    

        PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
        EXECUTE 'INSERT INTO  ' ||  quote_ident(mview.mv_name) || ' ' || mview.mv_query;
     END LOOP;

     PERFORM cs_log('Done refreshing materialized views.');
     RETURN 1;
END;
$$ LANGUAGE plpgsql;

キャプチャエラー

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
    RAISE NOTICE 'caught division_by_zero';
    RETURN x;
END;

カーソル宣言
PL/pgSQLでのカーソルへのアクセスはすべてカーソル変数によって実現され、常に特殊なデータ型refcursorです.カーソル変数を作成する方法の1つは、refcursorのタイプの変数として宣言することです.もう1つの方法は、カーソルを使用して構文を宣言することです.通常、次のようになります.

name CURSOR [ (arguments) ] FOR query;

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;

これら3つの変数はすべてタイプがrefcursorであるが、1つ目は任意のコマンドに使用でき、2つ目は既に
完全を宣言するコマンドをバインドし、最後にパラメータ付きのコマンドをバインドします.(keyはカーソルが開いたときに整数に置き換えられます.)
カーソルを開く
カーソルを使用してローを取得する前に、それを開く必要があります.

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

カーソル変数が開き、与えられたクエリが実行される.カーソルはすでに開いているわけではありません.バインドされていないカーソルとして宣言する必要があります.クエリーはSELECT、またはEXPLAINなどの他の戻り行でなければなりません.クエリーは
PL/pgSQLのSQL命令は平等に扱います
PL/pgSQLの変数名であり、実行計画は将来可能な多重化のためにキャッシュされる.

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

カーソル変数を開き、与えられたクエリーを実行します.カーソルはオープンではなく、バインドされていないカーソルとして宣言する必要があります.コマンドは、EXECUTEコマンドと同様の方法で宣言される文字列式です.これにより、コマンドが2回の実行間に変化する柔軟性が得られます.
バインドされたカーソルを開く

OPEN curs2;
OPEN curs3(42);

カーソルの使用

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;

FETCHカーソルから次の行をターゲットに取得します.ターゲットは、1つの行変数、1つのレコード変数、またはカンマで区切られた通常の変数のリストです.

CLOSE cursor;

CLOSEは、開いているカーソルの下に支持されているメッセージングを閉じる.これにより、トランザクションが終了する前にリソースを割り当てるか、後で再度開くためにカーソル変数を解放できます.

CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
       OPEN $1 FOR SELECT col FROM test;
       RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

ブロックで使用されるすべての変数は、1つのブロックの宣言セグメントで宣言する必要があります.(唯一の例外は、FORループ内のループ変数が整数範囲で反復され、整数変数として自動的に宣言されることです.