平田智剛のブログ

立法、行政、司法、報道、そして科学

F.ism.does_harm_to(!F);

動的SQLをpsqlだけで動かす

sql データベース postgreSQL

1. 静的SQLの限界

第1章の結論:
「表名や列名などを動的に指定することは、静的SQLでは無理」
(この意味が分かるのであれば、第1章を詳しく読む必要はない。
「表あ」と「表あ転置」に目を通し、第2章に進んでよい。)
　

表あは主キー「id」列及び列ア～ウから成っていて、idが1となるレコードを持っているものとする。
このレコードを90度回転させ、列方向に並べて表示させるSQLを考えてみよう。

DROP TABLE IF EXISTS 表あの列名を並べた複数行1列の表;
DROP TABLE IF EXISTS 表あ転置;
CREATE TABLE 表あの列名を並べた複数行1列の表(col text);
INSERT INTO 表あの列名を並べた複数行1列の表 VALUES ('列ア'),('列イ'),('列ウ');
CREATE TABLE 表あ転置 AS SELECT * FROM 表あの列名を並べた複数行1列の表;

-- 表あの列名を並べた複数行1列の表
--┌────┐
--│ col│
--┝━━━━┥
--│列ア│
--├────┤
--│列イ│
--├────┤
--│列ウ│
--└────┘

FOR 表あの各列名 IN SELECT col FROM 表あの列名を並べた複数行1列の表 LOOP
    UPDATE 表あ転置 
    SET col=
    (
        SELECT 表あの各列名 --※
        FROM 表あ 
        WHERE id=1
    ) 
    WHERE col=表あの各列名; --※
END LOOP;   

SELECT * FROM 表あ転置;

表あ (データ型はid以外すべてtext)

id (主キー integer) 列ア 列イ 列ウ
1 神奈川 藤沢 江の島
2 東京 足立 千住

表あ転置(想定)

col (text)
神奈川
藤沢
江の島

(「表あ転置」は、テーブルの未定義してあり、レコードは0件))

しかし、このSQL文は残念ながら思惑通りに動作しない。
FOR文内で、 表あの各列名 はループごとに 列ア → 列イ → 列ウ と変化して解釈されるが、
※を付した行では 表あの各列名 はそもそも解釈されない 。
SELECT 表あの各列名 FROM 表あ は、表あから「表あの各列名」という名前の列を探してこようとしてしまい、
「『表あの各列名』という名の列は、表あには存在しない」という旨のエラーを返してしまう。

このような問題は一般的に 表名や列名(や、データベース名もかな？)に変数を与えたときに発生する 。
ここでいう「変数を与える」というのを正確な表現で言うと、 動的に指定するということである。
表名や列名などを動的に指定することは、普通のSQL(静的SQL)では無理 なのである。

2. 動的SQLの導入

2-1. 動的SQLは埋め込みSQL

表名や列名を動的に指定する場合、「動的SQL」という方法をとる必要がある。
動的SQLは「SQLを実行した結果として別のSQL文を完成させ、その文を実行する」ということをしたいときに用いるワザである。
postgreSQL 11.5のドキュメンテーション には、次のように記述されている。

36.5. 動的SQL
多くの場合、アプリケーションが実行しなければならないSQL文は、アプリケーションを作成する段階で決まります。 しかし、中には、SQL文が実行時に構成されることや外部ソースで提供されることがあります。 このような場合、SQL文を直接Cソースコードに埋め込むことはできません。 しかし、文字列変数として提供される任意のSQL文を呼び出すことができる機能が存在します。

この引用文の主張はこうだ。SELECT 変数 のようにすることはできないが、str = 'SELECT' + 変数; のように 文字列としてのSQL文を生成 してからkaishaku(str);のように 文字列をSQL文として解釈する機能を利用すれば、同じことができる。
(kaishakuという関数は勝手につけた名前。実際には EXECUTE というのを使う)

ところで、この引用文の載っていた36章は「埋め込みSQL」について紹介する章である。
埋め込みSQLとは、「C言語など他の言語内に書かれたSQL」のことである。

少なくともpostgreSQLの場合、動的SQLを利用する場合は必ず埋め込みSQLを使うことになる。
(そもそも、kaishaku の機能を持つSQL文が存在しないからである)

2-2. psqlだけで動的SQLを実現させる

動的SQLは埋め込みSQLで扱わなければならず、埋め込みSQLは他の言語内に書かれたSQLを意味するのであれば、
三段論法より、 動的SQLは何らかのSQL以外の言語の中に書かなければならない ということになる。
このことから、一見すると、psqlだけでは動的SQLを扱うことができないように思われるだろう。
しかし、実際は psqlだけで動的SQLを扱うワザが存在する のである。
　
埋め込みSQLをほかの言語Lへ埋め込むとき、Lを埋め込み先言語と呼ぶことにしよう。
「psqlへ埋め込み可能な(psqlから呼び出し可能な)埋め込み先言語」へ埋め込みSQLを埋め込めば、psqlだけで動的SQLの扱いが完結するではないか。(図2.2.1)
f:id:ec084:20200120065618p:plain 図2.2.1 埋め込み先言語(赤)を介してpsqlへ二重に埋め込まれる動的SQL
　
実は、psqlでは「SQL手続き言語」と呼ばれる言語を呼び出すことができる のである。
本来は、SQL手続き言語を利用するためにはデータベースにその言語をインストールする必要があるが、
「PL/pgSQL」という言語はデフォルトですべてのデータベースにインストールされている ので、手動でのインストールが不要である。
(ドキュメンテーション42.1節より)
　
psqlからPL/pgSQLを呼び出すには、次のようにする。

DO 
$$
[<<ラベル名>>]
[DECLARE (PL/pgSQL変数名 型[ {:=|=} 値];)*]
BEGIN
    処理内容
END [ラベル名];
$$;

2-2-1. DO

DO $$hoge$$は、PL/pgSQLで書かれたhogeを実行するためのコマンドである。

ドキュメンテーション(SQLコマンドDOについて)には次のような記述がある。

DO
DO — 無名コードブロックを実行します。
　
概要
DO [ LANGUAGE lang_name ] code
説明
DOは無名コードブロック、言い換えると、手続き言語内の一時的な無名関数を実行します。
　
コードブロックはあたかもパラメータを取らずにvoidを返す関数の本体かのように扱われます。 これは解析され、一回実行されます。
　
LANGUAGE句をコードブロックの前または後ろにつけることができます。
　
パラメータ
code
実行される手続き言語のコードです。 これは、CREATE FUNCTIONの場合と同様、文字列リテラルとして指定しなければなりません。 ドル記号による引用符付けの使用を勧めます。
　
lang_name
コードの作成に使用する手続き言語の名前です。 省略時のデフォルトはplpgsqlです。
　
注釈
使用される手続き言語は、CREATE EXTENSIONを使用して現在のデータベースにインストール済みでなければなりません。 plpgsqlはデフォルトでインストールされますが、他の言語はインストールされません。

DO $$hoge$$ の $$は、文字列の開始及び終了を意味するものである。
クオーテーションでなくドルを用いることで、文字列中にクオーテーションが出てくる際にエスケープする必要がなくなる。

2-2-2. DECLARE／BEGIN／END

2-2-1節で述べた通り、DO直後の$$と$$に囲まれたDECLARE、BEGIN、ENDは、PL/pgSQL言語で書かれたものとみなされる。
したがって、カーソルを定義するSQLコマンドDECLARE、トランザクションを囲むSQLコマンドBEGINやENDとは一切関係ない ことに注意。
DECLARE の直後に 変数名 型; あるいは変数名 型 := 値; を書くことで変数を宣言できる。
また、BEGINとENDの間に、処理内容をPL/pgSQL言語のルールに則って記述する。

2-2-3. EXECUTEでSQLを利用する(まずは基本)

2-2-3-1. INTOもUSINGも使わない例

PL/pgSQLでSQLを扱うには、EXECUTE '文字列としてのSQL文'を利用する。

まずは静的SQLでもできるような簡単な内容を試してみよう。
psqlに以下の内容を打ち込むと、データベースに「テスト」表がつくられる。

DO
$$
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS テスト';
    EXECUTE 'CREATE TABLE テスト(col text)';
    EXECUTE 
    $inner$ 
        INSERT INTO テスト 
        VALUES('表「テスト」は、SQL中に埋め込んだPL/pgSQL言語の中にさらにSQLを埋め込んで作成した表である。')
    $inner$;
END;
$$;

SELECT * FROM テスト;

2-2-3-2. USINGを使う例(厄介)

EXECUTE '変数$1, $2, ...を使ったSQL文' USING 変数1, 変数2, ... を利用すると、実行するSQL文に変数を持たせることができることがある 。
2-2-3-1節と同じことをする例を次に示す。

DO
$$
DECLARE
    mytext text :='表「テスト」は、SQL中に埋め込んだPL/pgSQL言語の中にさらにSQLを埋め込んで作成した表である。';
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS テスト';
    EXECUTE 'CREATE TABLE テスト(col text)';
    EXECUTE 'INSERT INTO テスト VALUES($1)' USING mytext;
END;
$$;

SELECT * FROM テスト;

但し、USINGにより変数を後からはめ込むこの方法は、使える場所が限られている。
(他の場所でやると、そもそも解釈されず、「$1」などが残ってしまう。)
したがって、次のように文字列連結||を使う方が無難であると思う。

DO
$$
DECLARE
    mytext text :='表「テスト」は、SQL中に埋め込んだPL/pgSQL言語の中にさらにSQLを埋め込んで作成した表である。';
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS テスト';
    EXECUTE 'CREATE TABLE テスト(col text)';
    EXECUTE 'INSERT INTO テスト VALUES($$||mytext||$$)';
END;
$$;

SELECT * FROM テスト;

2-2-3-3. INTOを使う例

EXECUTE 'SQL文' INTO 結果を受け取る変数 を利用すると、SQL文により取得した結果の 先頭1行 を「結果を受け取る変数」に渡すことができる。
USINGと併用してもよい。
2-2-3-1節と同じことをする例を次に示す。

DO
$$
DECLARE
    mytext text :='表「テスト」は、SQL中に埋め込んだPL/pgSQL言語の中にさらにSQLを埋め込んで作成した表である。';
    myresult text;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS テスト';
    EXECUTE 'DROP TABLE IF EXISTS テスト2';
    EXECUTE 'CREATE TABLE テスト(col text)';
    EXECUTE 'CREATE TABLE テスト2(col text)';
    EXECUTE 'INSERT INTO テスト2 VALUES($1)' USING mytext;
    EXECUTE 'SELECT * FROM テスト2' INTO myresult;
    EXECUTE 'INSERT INTO テスト VALUES($1)' USING myresult ;
END;
$$;

SELECT * FROM テスト;

2-2-4. EXECUTEでSQLを利用する(psqlだけで行う動的SQLの実現)

では、第1章に示した表あから、表あ転置を生成する動的SQLを設計しよう。
第1章に示したコードと比較しながら読んでほしい。

(蛇足: FOR文も、実はPL/pgSQL言語のものであるため、第1章に示したコードは、--※を付した箇所の問題を訂正しただけでは、本当は動かない。)

DROP TABLE IF EXISTS 表あの列名を並べた複数行1列の表;
DROP TABLE IF EXISTS 表あ転置;
CREATE TABLE 表あの列名を並べた複数行1列の表(col text);
INSERT INTO 表あの列名を並べた複数行1列の表 VALUES ('列ア'),('列イ'),('列ウ');
CREATE TABLE 表あ転置 AS SELECT * FROM 表あの列名を並べた複数行1列の表;

-- 表あの列名を並べた複数行1列の表
--┌────┐
--│ col│
--┝━━━━┥
--│列ア│
--├────┤
--│列イ│
--├────┤
--│列ウ│
--└────┘

DO
$outer$
DECLARE
    表あの各列名 text;
BEGIN
    FOR 表あの各列名 IN SELECT col FROM 表あの列名を並べた複数行1列の表 
    LOOP
        EXECUTE 
        $$
            UPDATE 表あ転置 
            SET col=
            (
                SELECT $$||表あの各列名||$$ --※
                FROM 表あ 
                WHERE id=1
            ) 
            WHERE col='$$||表あの各列名||$$' --※
        $$ USING 表あの各列名;
    END LOOP;   
END;
$outer$;

SELECT * FROM 表あ転置;

このコードを動かすと、第1章に示した想定通りの「表あ転置」が得られる。

f:id:ec084:20200119205212p:plain
図2.2.4.1 動的SQLの比較図

3. 参考サイト:

https://improve-future.com/postgresql-execute-plpgsql-on-the-spot.html および、postgreSQLドキュメンテーション