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

<!-- this markdown file is arranged for qiita from hatenablog -->
# 1. 静的SQLの限界

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

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


```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のドキュメンテーション](https://www.postgresql.jp/document/11/html/) には、次のように記述されている。  

>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へ埋め込み可能な<sub>(psqlから呼び出し可能な)</sub>埋め込み先言語」へ埋め込みSQLを埋め込めば、psqlだけで動的SQLの扱いが完結するではないか。(図2.2.1)  
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/319477/4c8f0f24-166d-b455-f5c8-a46f6d03ee7e.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に以下の内容を打ち込むと、データベースに「テスト」表がつくられる。  

```sql
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節と同じことをする例を次に示す。

```sql
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」などが残ってしまう。)  
したがって、次のように文字列連結`||`を使う方が無難であると思う。


```sql
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節と同じことをする例を次に示す。

```sql
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章に示したコードは、`--※`を付した箇所の問題を訂正しただけでは、本当は動かない。)  

```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│
--┝━━━━┥
--│列ア│
--├────┤
--│列イ│
--├────┤
--│列ウ│
--└────┘

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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/319477/1952a198-c10b-b434-b194-c3b67ba0922c.png)

図2.2.4.1 動的SQLの比較図


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