サポートされている PL/pgSQL ステートメント - Amazon Redshift
代入SELECT INTOno-op動的 SQLreturn条件: IF 条件: CASEloopカーソルRAISEトランザクションの制御

サポートされている PL/pgSQL ステートメント

PL/pgSQL ステートメントは、SQL コマンドをプロシージャ構造体 (ループや条件式など) で強化し、論理フローを制御します。大半の SQL コマンドを使用できます。これには、COPY、UNLOAD、INSERT などのデータ操作言語 (DML) と CREATE TABLE などのデータ定義言語 (DDL) が含まれます。包括的な SQL コマンドのリストについては、「SQL コマンド」を参照してください。さらに、以下の PL/pgSQL ステートメントが Amazon Redshift でサポートされています。

代入

代入ステートメントは変数に値を代入します。式は単一の値を返す必要があります。

identifier := expression;

= の代わりに、標準ではない := を代入に使用することもできます。

式のデータ型が変数のデータ型に一致しない場合や、変数にサイズや精度が含まれている場合、結果の値は暗黙で変換されます。

次に例を示します。

customer_number := 20;
tip := subtotal * 0.15;

SELECT INTO

SELECT INTO ステートメントは、複数の列 (ただし 1 つの行) の結果をレコード変数、またはスカラー変数のリスト内に割り当てます。

SELECT INTO target select_expressions FROM ...;

上の構文で、target はレコード変数であるか、カンマ区切りのシンプルな変数やレコードフィールドのリストです。select_expressions リストおよびコマンドの残りは、通常の SQL と同じです。

変数リストを target として使用する場合、選択する値はターゲットの構造と厳密に一致する必要があります。そうでないと、ランタイムエラーが発生します。レコード変数がターゲットである場合、レコード変数はクエリの結果列の行タイプに自動的に設定されます。

INTO 句は、SELECT ステートメントのほとんどあらゆる場所で使用できます。通常は、SELECT 句の直後または FROM 句の直前で使用します。つまり、select_expressions リストの直前または直後に使用します。

クエリが 0 行を返した場合は、 NULL 値が target に割り当てられます。クエリが複数の行を返した場合は、最初の行が target に割り当てられ、残りは破棄されます。ステートメントに ORDER BY が含まれている場合を除いて、最初の行は決定的ではありません。

割り当てが少なくとも 1 行を返したかどうかを確認するには、特殊な FOUND 変数を使用します。

SELECT INTO customer_rec * FROM cust WHERE custname = lname;
IF NOT FOUND THEN
  RAISE EXCEPTION 'employee % not found', lname;
END IF;

レコード結果が NULL であるかどうかをテストするには、IS NULL 条件を使用できます。その他の行が廃棄されたかどうかを確認する方法はありません。次の例は、行が 1 つも返されない場合を示しています。

CREATE OR REPLACE PROCEDURE select_into_null(return_webpage OUT varchar(256))
AS $$
DECLARE
  customer_rec RECORD;
BEGIN
  SELECT INTO customer_rec * FROM users WHERE user_id=3;
  IF customer_rec.webpage IS NULL THEN
    -- user entered no webpage, return "http://"
    return_webpage = 'http://';
  END IF;
END;
$$ LANGUAGE plpgsql;

no-op

no-op ステートメント (NULL;) は何もしないプレースホルダステートメントです。no-op ステートメントは、IF-THEN-ELSE チェーンの 1 つの分岐が空であることを示す場合があります。

NULL;

動的 SQL

PL/pgSQL ストアドプロシージャから実行するたびに関与するテーブルやデータ型が異なる動的コマンドを生成するには、EXECUTE ステートメントを使用します。

EXECUTE command-string [ INTO target ];

上の構文で、command-string は実行対象のコマンドを含む文字列 (テキスト型) を生成する式です。この command-string の値は SQL エンジンに送信されます。コマンド文字列で PL/pgSQL 変数の代入は行われません。変数の値は、コマンド文字列の構築時に挿入する必要があります。

注記

動的 SQL 内から COMMIT ステートメント、および ROLLBACK ステートメントを使用することはできません。ストアドプロシージャ内での COMMIT ステートメントおよび ROLLBACK ステートメントの使用方法については、トランザクションの管理 を参照してください。

動的コマンドを使用する場合は、単一引用符のエスケープ処理が必要になることがあります。ドル引用符付けを使用して関数本体の固定テキストを引用符で囲むことをお勧めします。構築されたクエリ内に挿入する動的な値は、それ自体に引用符が含まれている場合があるため、特別な処理が必要になります。次の例では、関数全体のドル引用符付けを行うため、引用符を二重化する必要はありません。

EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = '
  || quote_literal(newvalue)
  || ' WHERE key = '
  || quote_literal(keyvalue);

上の例は、関数 quote_ident(text) と関数 quote_literal(text) を示しています。この例では、列およびテーブルの識別子を含む変数を quote_ident 関数に渡します。また、構築されたコマンドにリテラル文字列を含む変数を quote_literal 関数に渡します。どちらの関数も適切なステップに従って二重引用符または単一引用符で囲まれた入力テキストをそれぞれ返します。埋め込まれた特殊文字は適切にエスケープされます。

ドル引用符付けは固定テキストの引用符付けに限り有効です。上の例を次の形式で記述しないでください。

EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = $$'
  || newvalue
  || '$$ WHERE key = '
  || quote_literal(keyvalue);

これを行うと、newvalue の内容に $$ が含まれていた場合に、この例は破綻します。同じ問題は、他のすべてのドル引用符区切り記号を選択した場合にも該当します。事前に確認できないテキストに対して安全に引用符付けを行うには、quote_literal 関数を使用します。

return

RETURN ステートメントは、ストアドプロシージャから呼び出し元に戻ります。

RETURN;

例を以下に示します。

CREATE OR REPLACE PROCEDURE return_example(a int)
AS $$  
BEGIN
  FOR b in 1..10 LOOP
    IF b < a THEN
      RAISE INFO 'b = %', b;
    ELSE
      RETURN;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

条件: IF

IF 条件ステートメントは、Amazon Redshift で使用する PL/pgSQL 言語で以下の形式を取ることができます。

  • IF ..。THEN

    IF boolean-expression THEN
  statements
END IF;

    例を以下に示します。

    IF v_user_id <> 0 THEN
  UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

  • IF ..。THEN ..。ELSE

    IF boolean-expression THEN
  statements
ELSE
  statements
END IF;

    例を以下に示します。

    IF parentid IS NULL OR parentid = ''
THEN
  return_name = fullname;
  RETURN;
ELSE
  return_name = hp_true_filename(parentid) || '/' || fullname;
  RETURN;
END IF;

  • IF ..。THEN ..。ELSIF ..。THEN ..。ELSE

    キーワード ELSIF は ELSEIF と表記される場合もあります。

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

    例を以下に示します。

    IF number = 0 THEN
  result := 'zero';
ELSIF number > 0 THEN
  result := 'positive';
ELSIF number < 0 THEN
  result := 'negative';
ELSE
  -- the only other possibility is that number is null
  result := 'NULL';
END IF;

条件: CASE

CASE 条件ステートメントは、Amazon Redshift で使用する PL/pgSQL 言語で以下の形式を取ることができます。

  • シンプル CASE 

    CASE search-expression
WHEN expression [, expression [ ... ]] THEN
  statements
[ WHEN expression [, expression [ ... ]] THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;

    シンプル CASE ステートメントは、オペランドの等値に基づいて条件実行を提供します。

    search-expression の値は、1 回評価され、WHEN 句の expression と順次比較されます。一致が見つかると、対応する statements が実行されます。次に、END CASE の後に続くステートメントに制御が渡されます。後続の WHEN expressions は評価されません。一致が見つからない場合は、ELSE statements が実行されます。ただし、ELSE が存在しない場合は、CASE_NOT_FOUND 例外が発生します。

    例を以下に示します。

    CASE x
WHEN 1, 2 THEN
  msg := 'one or two';
ELSE
  msg := 'other value than one or two';
END CASE;

  • 検索 CASE 

    CASE
WHEN boolean-expression THEN
  statements
[ WHEN boolean-expression THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;

    検索形式の CASE は、Boolean 式の真理値に基づいて条件実行を提供します。

    WHEN 句の boolean-expression のいずれかが真を生成するまで順に評価されます。次に、対応するステートメントが実行され、その後に END CASE に続くステートメントに制御が渡されます。後続の WHEN expressions は評価されません。真の結果が見つからない場合は、ELSE statements が実行されます。ただし、ELSE が存在しない場合は、CASE_NOT_FOUND 例外が発生します。

    例を以下に示します。

    CASE
WHEN x BETWEEN 0 AND 10 THEN
  msg := 'value is between zero and ten';
WHEN x BETWEEN 11 AND 20 THEN
  msg := 'value is between eleven and twenty';
END CASE;

loop

loop ステートメントは、Amazon Redshift で使用する PL/pgSQL 言語で以下の形式を取ることができます。

  • simple loop 

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

    simple loop は、EXIT または RETURN ステートメントによって終了されるまで無制限に繰り返される無条件ループを定義します。EXIT および CONTINUE ステートメントは、ネストされたループ内でオプションのラベルを使用することで、参照先のループを指定できます。

    例を以下に示します。

    CREATE OR REPLACE PROCEDURE simple_loop()
LANGUAGE plpgsql
AS $$
BEGIN
  <<simple_while>>
  LOOP
    RAISE INFO 'I am raised once';  
    EXIT simple_while;
    RAISE INFO 'I am not raised';
  END LOOP;
  RAISE INFO 'I am raised once as well';
END;
$$;

  • Exit loop

    EXIT [ label ] [ WHEN expression ];

    label が存在しない場合は、最も内側のループが終了され、END LOOP に続くステートメントが次に実行されます。label が存在する場合、それはネストされたループやブロックの現在のレベルまたは他の外側のレベルのラベルであることが必要です。次に、指名されたループやブロックが終了され、そのループやブロックの対応する END に続くステートメントに制御が移ります。

    WHEN が指定されている場合は、expression が真である場合に限り、ループの終了が起こります。それ以外の場合は、EXIT の後に続くステートメントに制御が移ります。

    EXIT はすべてのタイプのループで使用できます。無条件ループでの使用に限定されません。

    BEGIN ブロックと一緒に使用した場合、EXIT はブロックの最後に続くステートメントに制御を渡します。この目的のためにラベルを使用する必要があります。ラベルなしの EXIT は、BEGIN ブロックに一致するとは見なされません。

    例を以下に示します。

    CREATE OR REPLACE PROCEDURE simple_loop_when(x int)
LANGUAGE plpgsql
AS $$
DECLARE i INTEGER := 0;
BEGIN
  <<simple_loop_when>>
  LOOP
    RAISE INFO 'i %', i;
    i := i + 1;
    EXIT simple_loop_when WHEN (i >= x);
  END LOOP;
END;
$$;

  • Continue loop 

    CONTINUE [ label ] [ WHEN expression ];

    label が存在しない場合、実行は最も内側であるループの次の反復処理にジャンプします。つまり、ループ本体に残っているすべてのステートメントはスキップされます。この場合、制御はループ制御式 (ある場合) に戻り、別のループ反復処理が必要であるかどうかが確認されます。label が存在する場合は、実行を継続するループのラベルが指定されます。

    WHEN が指定されている場合は、expression が真であるときに限り、次のループ反復処理が開始されます。指定されていない場合は、CONTINUE の後に続くステートメントに制御が移ります。

    CONTINUE はすべてのタイプのループで使用できます。無条件ループでの使用に限定されません。

    CONTINUE mylabel;

  • WHILE loop 

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

    WHILE ステートメントは、boolean-expression の評価が真である間は、ステートメントのシーケンスを繰り返します。式は、ループ本体に入る直前にチェックされます。

    例を以下に示します。

    WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
  -- some computations here
END LOOP;

WHILE NOT done LOOP
  -- some computations here
END LOOP;

  • FOR loop (整数バリアント) 

    [<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
  statements
END LOOP [ label ];

    FOR loop (整数バリアント) は、整数値の範囲を反復処理するループを作成します。変数 name は整数型として自動的に定義され、ループ内にのみ存在します。変数 name の既存の定義はループ内では無視されます。範囲の下限と上限を指定する 2 つの式は、ループに入るときに 1 回評価されます。EVERSE を指定すると、各反復処理後に、ステップ値は加算されずに減算されます。

    下限が上限より大きい (REVERSE の場合はより小さい) と、ループ本体は実行されません。エラーは発生しません。

    ラベルが FOR ループにアタッチされている場合は、そのラベルを使用して、修飾名で整数ループ変数を参照できます。

    例を以下に示します。

    FOR i IN 1..10 LOOP
  -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
  -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

  • FOR loop (結果セットバリアント) 

    [<<label>>]
FOR target IN query LOOP
  statements
END LOOP [ label ];

    target はレコード変数であるか、カンマ区切りのスカラー変数のリストです。target には、query の結果である各行が順次代入され、行ごとにループ本体が実行されます。

    FOR loop (結果セットバリアント) を使用すると、ストアドプロシージャはクエリの結果を反復処理し、そのデータを適切に操作できます。

    例を以下に示します。

    CREATE PROCEDURE cs_refresh_reports() AS $$
DECLARE
  reports RECORD;
BEGIN
  FOR reports IN SELECT * FROM cs_reports ORDER BY sort_key LOOP
    -- Now "reports" has one record from cs_reports
    EXECUTE 'TRUNCATE TABLE ' || quote_ident(reports.report_name);
    EXECUTE 'INSERT INTO ' || quote_ident(reports.report_name) || ' ' || reports.report_query;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

  • FOR loop (動的 SQL を使用)

    [<<label>>]
FOR record_or_row IN EXECUTE text_expression LOOP 
  statements
END LOOP;

    動的 SQL を使用する FOR loop の場合、ストアドプロシージャは動的クエリの結果を反復処理し、そのデータを適切に処理できます。

    例を以下に示します。

    CREATE OR REPLACE PROCEDURE for_loop_dynamic_sql(x int)
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  query text;
BEGIN
  query := 'SELECT * FROM tbl_dynamic_sql LIMIT ' || x;
  FOR rec IN EXECUTE query
  LOOP
    RAISE INFO 'a %', rec.a;
  END LOOP;
END;
$$;

カーソル

クエリ全体を一度に実行せずに、カーソルを設定できます。カーソル は、クエリをカプセル化し、クエリの結果を一度に数行ずつ読み取ります。これを行う理由の 1 つは、結果内に多数の行がある場合のメモリの枯渇を防ぐことです。別の理由は、呼び出し元が行を読み取ることができるように、ストアドプロシージャが作成したカーソルへの参照を返すことです。これにより、ストアドプロシージャから大きな行セットを返す際の効率が向上します。

NONATOMIC ストアドプロシージャでカーソルを使用するには、START TRANSACTION...COMMIT の間にカーソルループを置きます。

カーソルを設定するには、まずカーソル変数を宣言します。PL/pgSQL では、カーソルへのすべてのアクセスがカーソル変数を経由します。カーソル変数は常に特殊な refcursor データ型です。refcursor データ型は単にカーソルへの参照を保持します。

カーソル変数を作成するには、refcursor 型の変数としてカーソルを宣言します。または、次に示すカーソル宣言構文を使用することもできます。

name CURSOR [ ( arguments ) ] FOR query ;

上の構文で、arguments (指定されている場合) はカンマ区切りの name datatype ペアのリストです。各ペアは、query のパラメータ値で置き換えられる名前を定義します。これらの名前と置き換わる実際の値は、後で、カーソルを開いたときに指定されます。

次に例を示します。

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

これら 3 つすべての変数は refcursor データ型を持ちますが、最初の変数はすべてのクエリで使用できます。一方、2 番目の変数には完全に指定されたクエリがバインドされており、3 番目の変数にはパラメータ化クエリがバインドされています。key 値は、カーソルを開いたときに、整数パラメータ値に置き換えられます。変数 curs1 は、特定のクエリにバインドされていないため、非バインドと呼ばれます。

カーソルを使用して行を取得する前に、カーソルを開く必要があります。PL/pgSQL には 3 つの形式の OPEN ステートメントがあります。そのうちの 2 つでは、非バインドカーソル変数を使用し、残りの 1 つではバインドカーソル変数を使用します。

  • Open for select: カーソル変数を開き、これに対して実行するクエリを指示します。すでに開いているカーソルを開くことはできません。また、カーソルは非バインドカーソル (つまり、シンプルな refcursor 変数) として宣言済みであることが必要です。SELECT クエリは、PL/pgSQL の他の SELECT ステートメントと同じ方法で扱われます。

    OPEN cursor_name FOR SELECT ...;

    例を以下に示します。

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

  • Open for execute: カーソル変数を開き、これに対して実行するクエリを指示します。すでに開いているカーソルを開くことはできません。また、カーソルは非バインドカーソル (つまり、シンプルな refcursor 変数) として宣言済みであることが必要です。クエリは、EXECUTE コマンドと同じ方法で、文字列式として指定されます。これに伴う柔軟性により、今回実行したものとは異なるクエリを次回に実行できます。

    OPEN cursor_name FOR EXECUTE query_string;

    例を以下に示します。

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

  • Open a bound cursor: この形式の OPEN では、宣言時にクエリがバインド済みであるカーソル変数を開きます。すでに開いているカーソルを開くことはできません。実際の引数値式が表示されるのは、カーソルが引数を取るように宣言済みである場合に限る必要があります。これらの値はクエリ内で置き換えられます。

    OPEN bound_cursor_name [ ( argument_values ) ];

    例を以下に示します。

    OPEN curs2;
OPEN curs3(42);

カーソルが開いたら、以下に説明するステートメントを使用してカーソルを操作できます。これらのステートメントは、カーソルを開いた同じストアドプロシージャで使用する必要はありません。ストアドプロシージャから refcursor 値を返して、呼び出し元でカーソルを操作できます。すべてのポータルは、トランザクションの終わりに暗黙で閉じられます。したがって、refcursor 値を使用して開いているカーソルを参照できるのは、トランザクションの終わりまでです。

  • FETCH は、カーソルから次に続く行を取得して target に格納します。この target は、SELECT INTO の場合と同じように、行変数、レコード変数、カンマ区切りのシンプルな変数のリストのいずれかです。SELECT INTO の場合と同様に、特殊な FOUND 変数をチェックして、行が取得されたかどうかを確認できます。

    FETCH cursor INTO target;

    例を以下に示します。

    FETCH curs1 INTO rowvar;

  • CLOSE は、開いているカーソルの基になっているポータルを閉じます。このステートメントを使用してトランザクションの終了より前にリソースを解放できます。また、このステートメントを使用してカーソル変数を解放し、再度開くこともできます。

    CLOSE cursor;

    例を以下に示します。

    CLOSE curs1;

RAISE

RAISE level ステートメントを使用して、メッセージを報告してエラーをレイズします。

RAISE level 'format' [, variable [, ...]];

使用可能なレベルは、NOTICE、INFO、LOG、WARNING、および EXCEPTION です。EXCEPTION はエラーを発生させ、このエラーは通常、現行のトランザクションをキャンセルします。その他のレベルでは、さまざまな優先度レベルのメッセージのみを生成します。

format 文字列内の % は、次の省略可能な引数の文字列表現に置き換えられます。リテラル % を表すには「%%」と記述します。現在、省略可能な引数はシンプルな変数にする必要があり (式ではなく)、format はシンプルな文字列リテラルにする必要があります。

次の例では、文字列内の % が v_job_id の値に置き換えられます。

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

RAISE ステートメントを使用して、例外処理ブロックでキャッチされた例外を再スローします。このステートメントは、NONATOMIC モードのストアドプロシージャの例外処理ブロックでのみ有効です。

RAISE;

トランザクションの制御

Amazon Redshift で使用する PL/pgSQL 言語のトランザクション制御ステートメントを使用できます。ストアドプロシージャ内での COMMIT、ROLLBACK、TRUNCATE ステートメントの使用方法については、トランザクションの管理 を参照してください。

NONATOMIC モードのストアドプロシージャでは、START TRANSACTION を使用してトランザクションブロックを開始します。

START TRANSACTION;
注記

PL/pgSQL ステートメントの START TRANSACTION は、SQL コマンドの START TRANSACTION と次の点で異なります。

  • ストアドプロシージャ内では、START TRANSACTION は BEGIN と同義ではありません。

  • PL/pgSQL ステートメントは、オプションの分離レベルとアクセス許可キーワードをサポートしていません。