LoginSignup
16
16

More than 3 years have passed since last update.

@17ec084(技術部アカウント 特定非営利活動法人日本弱者男性センター)

【postgreSQL】動的SQLをpsqlだけで動かす

Last updated at Posted at 2020-01-19

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)

image.png

図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章に示した想定通りの「表あ転置」が得られる。

image.png

図2.2.4.1 動的SQLの比較図

3. 参考サイト:

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

16
16
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
16
16