\c postgres <%= @pg_role %>
CREATE EXTENSION treasuredata_fdw;
CREATE SERVER treasuredata_server FOREIGN DATA WRAPPER treasuredata_fdw;
CREATE FOREIGN TABLE td_www_access (
    time integer,
    code integer,
    size integer,
    method varchar
)
SERVER treasuredata_server OPTIONS (
    apikey '<%= @apikey %>',
    database 'sample_datasets',
    query_engine '<%= @query_engine %>',
    table 'www_access'
);
SELECT * FROM td_www_access ORDER BY TIME LIMIT 5;
    time    | code | size | method 
------------+------+------+--------
 1412320845 |  200 |   62 | GET
 1412320861 |  200 |  115 | GET
 1412320878 |  200 |   75 | GET
 1412320895 |  200 |   50 | GET
 1412320911 |  200 |   93 | GET
(5 rows)

SELECT COUNT(1) FROM td_www_access;
 count 
-------
  5000
(1 row)

SELECT COUNT(1) FROM td_www_access WHERE time < 1412320911;
 count 
-------
     4
(1 row)

SELECT COUNT(1) FROM td_www_access WHERE SUBSTR(method, 1, 3) = 'POS';
 count 
-------
   376
(1 row)

SELECT COUNT(1) FROM td_www_access WHERE method LIKE '%ET' AND code = 200;
 count 
-------
  4605
(1 row)

SELECT COUNT(1) FROM td_www_access WHERE method NOT LIKE '%ET' AND code NOT IN (404, 409);
 count 
-------
   376
(1 row)

SELECT COUNT(1) FROM td_www_access WHERE code = ANY('{200,400}') AND method = SOME('{"","DELETE,GET","POST"}');
 count 
-------
   376
(1 row)

SELECT COUNT(1) FROM td_www_access WHERE time in (SELECT MIN(time) FROM td_www_access);
 count 
-------
     1
(1 row)

SELECT COUNT(1) FROM td_www_access WHERE SUBSTRING(method, 1, 2) || SUBSTR(method, 3, 2) = 'POST';
 count 
-------
   376
(1 row)

-- For https://github.com/komamitsu/treasuredata_fdw/issues/23
SELECT COUNT(1) FROM (SELECT * FROM td_www_access LIMIT 1) x WHERE size > 0;
 count 
-------
     1
(1 row)

CREATE FOREIGN TABLE td_summary_in_www_access (
    code integer,
    method varchar,
    count integer
)
SERVER treasuredata_server OPTIONS (
    apikey '<%= @apikey %>',
    database 'sample_datasets',
    query_engine '<%= @query_engine %>',
    query 'SELECT code, method, COUNT(1) as count FROM www_access GROUP BY code, method'
);
SELECT * FROM td_summary_in_www_access WHERE method = 'GET' AND (code = 200 OR code = 404) ORDER BY code, method;
 code | method | count 
------+--------+-------
  200 | GET    |  4605
  404 | GET    |    17
(2 rows)

SELECT method, code FROM td_summary_in_www_access ORDER BY code, method;
 method | code 
--------+------
 GET    |  200
 POST   |  200
 GET    |  404
 GET    |  500
(4 rows)

CREATE FOREIGN TABLE td_www_access_dst (
    time integer,
    code integer,
    size integer,
    method varchar
)
SERVER treasuredata_server OPTIONS (
    apikey '<%= @apikey %>',
    database 'treasuredata_fdw',
    query_engine '<%= @query_engine %>',
    table 'www_access_dst',
    import_file_size '50000',
    atomic_import 'true'
);
INSERT INTO td_www_access_dst SELECT * FROM td_www_access;
CREATE FOREIGN TABLE td_array_test (
    str_array text[],
    int_array integer[],
    long_array bigint[],
    float_array float[],
    double_array float[],
    str_array_array text[][],
    int_array_array integer[][],
    long_array_array bigint[][],
    float_array_array float[][],
    double_array_array float[][]
)
SERVER treasuredata_server OPTIONS (
    apikey '<%= @apikey %>',
    database 'treasuredata_fdw',
    query_engine '<%= @query_engine %>',
    table 'array_test'
);
SELECT * FROM td_array_test;
    str_array    | int_array |             long_array             |  float_array  |    double_array     |     str_array_array      | int_array_array |                 long_array_array                  |   float_array_array   |      double_array_array       
-----------------+-----------+------------------------------------+---------------+---------------------+--------------------------+-----------------+---------------------------------------------------+-----------------------+-------------------------------
 {one,two,three} | {1,2,3}   | {1000000000,2000000000,3000000000} | {1.1,2.2,3.3} | {1.111,2.222,3.333} | {{one,two},{three,four}} | {{1,2},{3,4}}   | {{1000000000,2000000000},{3000000000,4000000000}} | {{1.1,2.2},{3.3,4.4}} | {{1.111,2.222},{3.333,4.444}}
(1 row)

SELECT COUNT(1) FROM td_array_test WHERE 'two' = any(str_array);
 count 
-------
     1
(1 row)

DROP FOREIGN TABLE td_www_access_dst;
DROP FOREIGN TABLE td_summary_in_www_access;
DROP FOREIGN TABLE td_www_access;
DROP FOREIGN TABLE td_array_test;
DROP SERVER treasuredata_server;
DROP EXTENSION treasuredata_fdw CASCADE;