PL/pgSQL の構造 - Amazon Redshift
ブロック変数宣言エイリアス宣言組み込み変数レコード型

PL/pgSQL の構造

PL/pgSQL は手続き言語であり、他の手続き言語と同じ構造体が多数含まれています。

ブロック

PL/pgSQL はブロック構造言語です。プロシージャの完全な本文は、ブロック内で定義されます。ブロックには、変数宣言と PL/pgSQL ステートメントが含まれます。ステートメントは、ネストされたブロックまたはサブブロックである場合もあります。

宣言とステートメントはセミコロンで終了します。ブロックまたはサブブロックの END キーワードの後にセミコロンを続けます。DECLARE キーワードと BEGIN キーワードの後にセミコロンを使用しないでください。

すべてのキーワードと識別子は大文字と小文字を混用して記述できます。識別子は、二重引用符で囲まれていない場合、暗黙で小文字に変換されます。

二重ハイフン (--) は、行末までのコメントを開始します。/* は、次の */ が発生するまでのブロックコメントを開始します。ブロックコメントはネストできません。ただし、二重ハイフンコメントはブロックコメントで囲むことができます。また、二重ハイフンはブロックコメントの区切り記号 /* および */ を隠すことができます。

ブロックのステートメントセクションのいずれのステートメントもサブブロックにすることができます。サブブロックを使用して論理グループを構成したり、変数を小グループのステートメントにローカライズしたりできます。

[ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
END [ label ];

ブロックに先行する宣言セクションで宣言された変数は、ブロックが入力されるたびにデフォルト値に初期化されます。つまり、初期化される回数は関数呼び出しごとに 1 回とは限りません。

例を以下に示します。

CREATE PROCEDURE update_value() AS $$
DECLARE
  value integer := 20;
BEGIN
  RAISE NOTICE 'Value here is %', value;  -- Value here is 20
  value := 50;
  --
  -- Create a subblock
  --
  DECLARE
    value integer := 80;
  BEGIN
    RAISE NOTICE 'Value here is %', value;  -- Value here is 80
  END;

  RAISE NOTICE 'Value here is %', value;  -- Value here is 50
END;
$$ LANGUAGE plpgsql;

EXIT ステートメントで使用するブロックを識別したり、ブロックで宣言する変数の名前を修飾したりするには、ラベルを使用します。

PL/pgSQL でステートメントをグループ分けする BEGIN/END と、トランザクションコントロール用のデータベースコマンドを混同しないでください。PL/pgSQL の BEGIN および END は単にグループ分けするためのものです。トランザクションを開始または終了することはありません。

変数宣言

ループ変数を例外として、ブロック内のすべての変数をブロックの DECLARE セクションで宣言します。変数は任意の有効な Amazon Redshift データ型を使用できます。サポートされているデータ型については、データ型 を参照してください。

PL/pgSQL 変数は Amazon Redshift でサポートされている任意のデータ型と、さらに RECORD および refcursor を使用できます。RECORD の詳細については、レコード型 を参照してください。refcursor の詳細については、カーソル を参照してください。

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

変数宣言の例を以下に示します。

customerID integer;
numberofitems numeric(6);
link varchar;
onerow RECORD;

整数の範囲を反復処理する FOR ループのループ変数は、自動的に整数変数として宣言されます。

DEFAULT 句 (ある場合) は、ブロックの入力時に変数に代入する初期値を指定します。DEFAULT 句がない場合、変数は SQL の NULL 値に初期化されます。CONSTANT オプションは、変数への代入を禁止して、ブロックの存続期間中、変数の値を一定に維持します。NOT NULL を指定すると、NULL 値を代入したときにランタイムエラーが発生します。NOT NULL として宣言されたすべての変数には、NULL 以外のデフォルト値を指定する必要があります。

デフォルト値はブロックを入力するたびに評価されます。例えば、now() を timestamp 型の変数に代入すると、変数は関数がプリコンパイルされた時刻ではなく、現在の関数呼び出しの時刻になります。

quantity INTEGER DEFAULT 32;
url VARCHAR := 'http://mysite.com';
user_id CONSTANT INTEGER := 10;

refcursor データ型は、ストアドプロシージャ内のカーソル変数のデータ型です。refcursor の値はストアドプロシージャ内から返される場合があります。詳細については、「 」を参照してください結果セットを返す

エイリアス宣言

ストアドプロシージャの署名から引数名が省略されている場合、その引数のエイリアスを宣言できます。

name ALIAS FOR $n;

組み込み変数

以下の組み込み変数がサポートされています。

  • FOUND

  • SQLSTATE

  • SQLERRM

  • GET DIAGNOSTICS integer_var := ROW_COUNT;

FOUND は Boolean 型の特殊な変数です。FOUND は各プロシージャ呼び出しで最初は false に設定されます。FOUND は以下のタイプのステートメントで設定されます。

  • SELECT INTO

    行を返す場合は FOUND を true に設定し、行を返さない場合は false に設定します。

  • UPDATE、INSERT、および DELETE

    少なくとも 1 つの行が影響を受ける場合は FOUND を true に設定し、どの行も影響を受けない場合は false に設定します。

  • FETCH

    行を返す場合は FOUND を true に設定し、行を返さない場合は false に設定します。

  • FOR ステートメント

    FOR ステートメントが 1 回以上反復処理を行う場合は FOUND を true に設定し、それ以外の場合は false に設定します。これは FOR ステートメントの 3 つすべてのバリアント (整数 FOR ループ、レコードセット FOR ループ、動的レコードセット FOR ループ) に該当します。

    FOUND は FOR ループの終了時に設定されます。ループのランタイム内では、FOUND は FOR ステートメントによって変更されません。ただし、ループ本体内の他のステートメントの実行によって変更される場合があります。

例を以下に示します。

CREATE TABLE employee(empname varchar);
CREATE OR REPLACE PROCEDURE show_found()
AS  $$
DECLARE
  myrec record;
BEGIN
  SELECT INTO myrec * FROM employee WHERE empname = 'John';
  IF NOT FOUND THEN
    RAISE EXCEPTION 'employee John not found';
  END IF;
END;
$$ LANGUAGE plpgsql;

例外ハンドラ内では、特殊な変数 SQLSTATE に、発生した例外に対応するエラーコードが含まれます。特殊な変数 SQLERRM には、例外に関連するエラーメッセージが含まれます。これらの変数は、例外ハンドラー外では未定義であり、使用した場合はエラーが表示されます。

例を以下に示します。

CREATE OR REPLACE PROCEDURE sqlstate_sqlerrm() AS
$$
BEGIN
  UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
  EXECUTE 'select invalid';
  EXCEPTION WHEN OTHERS THEN
  RAISE INFO 'error message SQLERRM %', SQLERRM;
  RAISE INFO 'error message SQLSTATE %', SQLSTATE;
END;
$$ LANGUAGE plpgsql;

ROW_COUNT が GET DIAGNOSTICS コマンドと一緒に使用されています。これは、SQL エンジンに送信された最後の SQL コマンドで処理された行の数を示します。

例を以下に示します。

CREATE OR REPLACE PROCEDURE sp_row_count() AS
$$
DECLARE
  integer_var int;
BEGIN
  INSERT INTO tbl_row_count VALUES(1);
  GET DIAGNOSTICS integer_var := ROW_COUNT;
  RAISE INFO 'rows inserted = %', integer_var;
END;
$$ LANGUAGE plpgsql;

レコード型

RECORD 型は、実際のデータ型ではなく、単なるプレースホルダです。レコード型の変数は、SELECT または FOR コマンドの実行中に割り当てられた行の実際の行構造を取ります。レコード変数のサブ構造は、値が割り当てられるたびに変わる場合があります。レコード変数が最初に割り当てられるまでは、変数にサブ構造はありません。サブ構造のフィールドにアクセスしようとすると、ランタイムエラーが発生します。

name RECORD;

例を以下に示します。

CREATE TABLE tbl_record(a int, b int);
INSERT INTO tbl_record VALUES(1, 2);
CREATE OR REPLACE PROCEDURE record_example()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT a FROM tbl_record
  LOOP
    RAISE INFO 'a = %', rec.a;
  END LOOP;
END;
$$;