php配列をpostgresql の配列型へINSERT

いろいろ調査してみましたが、そんなに都合よくバインドしてくれるものは、ありませんでした。
ちなみにしたいことを簡単に説明すると料金テーブルがあり、そこには大人と子供の料金を配列もつカラムがあり一週間分のデータ配列でもちたい。

CREATE TABLE public.price
(
  price_id integer,
  adult    integer[],
  child integer[]
);

登録するときにphpの配列で持っている料金をINSERTしたい。

INSERT文はこんな感じ

INSERT INTO price(price_id, adult, child) VALUES (1, '{100,100,NULL,100,100,200,300}', '{50,50,NULL,50,50,100,150}');

phpの配列を直接バインドするいいものが、みつからない。postgresの配列は文字列みたいなものなのでINSERTと同じようかたちで文字列をつくり、バインドしてしまえばよいかと。以下のソースはバインドするのでなく直接か埋め込んでいますが、参考までにソースをメモ

    <?php

    $conn = "host=localhost dbname=hoge user=postgres password=postgres";
    $link = pg_connect($conn);
    if (!$link) {
        die('接続失敗'.pg_last_error());
    }

    print('接続成功');
    print('データを追加します');

    $price_arr = array();
    $price_arr['adult'][] = 100;
    $price_arr['adult'][] = 100;
    $price_arr['adult'][] = 'NULL';    //NULLという文字列を仕込む
    $price_arr['adult'][] = 100;
    $price_arr['adult'][] = 100;
    $price_arr['adult'][] = 200;
    $price_arr['adult'][] = 300;

    $price_arr['child'][] = 100;
    $price_arr['child'][] = 100;
    $price_arr['child'][] = 'NULL';    //NULLという文字列を仕込む
    $price_arr['child'][] = 100;
    $price_arr['child'][] = 100;
    $price_arr['child'][] = 200;
    $price_arr['child'][] = 300;


    $adult = '{'.implode(',', $price_arr['adult']).'}';
    $child = '{'.implode(',', $price_arr['child']).'}';

    $sql = "INSERT INTO price (price_id, adult, child) VALUES (1, '{$adult}', '{$child}')";
    $result_flag = pg_query($sql);

    if (!$result_flag) {
        die('INSERTクエリー失敗'.pg_last_error());
    }

    $close_flag = pg_close($link);

    if ($close_flag){
        print('切断成功');
    }

データをみてみると

joptional=# SELECT * FROM price;
 price_id |             adult              |             child
----------+--------------------------------+--------------------------------
        1 | {100,100,NULL,100,100,200,300} | {100,100,NULL,100,100,200,300}

きちんと登録されていました。

こうやって登録してしまえば、こんなデータ取得方法もできる。

joptional=# SELECT price_id, unnest(adult),unnest(child) FROM price;
 price_id | unnest | unnest
----------+--------+--------
        1 |    100 |    100
        1 |    100 |    100
        1 |        |
        1 |    100 |    100
        1 |    100 |    100
        1 |    200 |    200
        1 |    300 |    300

バッチとかであれば、配列文字列をSQLで組み立てることも可能。

いちど、配列をunnestで分解したものをもとに戻す方法

SELECT
 price_id
,ARRAY_AGG(adult)
,ARRAY_AGG(child)
FROM
(
      SELECT
      price_id
     ,unnest(adult) AS adult
     ,unnest(child) AS child
      FROM price
)tmp
GROUP BY price_id



 price_id |           array_agg            |           array_agg
----------+--------------------------------+--------------------------------
        1 | {100,100,NULL,100,100,200,300} | {100,100,NULL,100,100,200,300}