\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,
    agent varchar,
    size integer,
    method varchar
)
SERVER treasuredata_server OPTIONS (
    apikey '<%= @apikey %>',
    database 'sample_datasets',
    query_engine '<%= @query_engine %>',
    table 'www_access'
);
SELECT time, code, size, method FROM td_www_access ORDER BY TIME LIMIT 5;
SELECT COUNT(1) FROM td_www_access;
SELECT COUNT(1) FROM td_www_access WHERE time < 1412320911;
SELECT COUNT(1) FROM td_www_access WHERE SUBSTR(method, 1, 3) = 'POS';
SELECT COUNT(1) FROM td_www_access WHERE method LIKE '%ET' AND code = 200;
SELECT COUNT(1) FROM td_www_access WHERE method NOT LIKE '%ET' AND code NOT IN (404, 409);
SELECT COUNT(1) FROM td_www_access WHERE code = ANY('{200,400}') AND method = SOME('{"","DELETE,GET","POST"}');
SELECT COUNT(1) FROM td_www_access WHERE code > ANY('{200, 300}');
SELECT COUNT(1) FROM td_www_access WHERE code = 200 AND method IN ('"', 'GET', 'POST');
SELECT COUNT(1) FROM td_www_access WHERE time in (SELECT MIN(time) FROM td_www_access);
SELECT COUNT(1) FROM td_www_access WHERE SUBSTRING(method, 1, 2) || SUBSTR(method, 3, 2) = 'POST';
SELECT COUNT(1) FROM td_www_access WHERE agent = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:9.0.1) Gecko/20100101 Firefox/9.0.1' OR agent LIKE 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:10.0.1)%' OR agent IN ('Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)', 'Mozilla/5.0 (iPad; CPU OS 5_0_1 like Mac OS X) AppleWebKit/534.46 (KHTML, like Gecko) Version/5.1 Mobile/9A405 Safari/7534.48.3');
-- For https://github.com/komamitsu/treasuredata_fdw/issues/23
SELECT COUNT(1) FROM (SELECT * FROM td_www_access LIMIT 1) x WHERE size > 0;
BEGIN;
DECLARE cur CURSOR FOR SELECT time FROM td_www_access WHERE time < 1412320911 ORDER BY time;
FETCH cur;
FETCH cur;
COMMIT;
CREATE FOREIGN TABLE td_quote_test (
    val varchar
)
SERVER treasuredata_server OPTIONS (
    apikey '<%= @apikey %>',
    database 'treasuredata_fdw',
    query_engine '<%= @query_engine %>',
    table 'quote_test'
);
SELECT COUNT(1) FROM td_quote_test WHERE val IN ('xxx', '''abc''');
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_download_dir '/tmp/tdfdw',
    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;
SELECT method, code FROM td_summary_in_www_access ORDER BY code, method;
CREATE FOREIGN TABLE td_www_access_dst (
    time integer,
    code integer,
    agent varchar,
    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;
SELECT COUNT(1) FROM td_array_test WHERE 'two' = any(str_array);
-- ===================================================================
-- import foreign schema
-- ===================================================================
CREATE SCHEMA local_schema;
IMPORT FOREIGN SCHEMA sample_datasets
FROM SERVER treasuredata_server
INTO local_schema OPTIONS (
    apikey '<%= @apikey %>',
    query_engine '<%= @query_engine %>'
);
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'local_schema'
ORDER BY table_name, ordinal_position;
SELECT code, size, method FROM local_schema.www_access ORDER BY size, code LIMIT 5;
IMPORT FOREIGN SCHEMA treasuredata_fdw LIMIT TO (array_test)
FROM SERVER treasuredata_server
INTO local_schema OPTIONS (
    apikey '<%= @apikey %>',
    query_engine '<%= @query_engine %>'
);
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'local_schema' AND table_name = 'array_test'
ORDER BY table_name, ordinal_position;
SELECT * FROM local_schema.array_test;
SELECT COUNT(1) FROM local_schema.array_test WHERE 'two' = any(str_array);
-- ===================================================================
-- clean up
-- ===================================================================
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_quote_test;
DROP FOREIGN TABLE td_array_test;
SET client_min_messages = 'error'; -- pg9.6 print NOTICE log in the next command but pg9.5 does not
DROP SCHEMA local_schema CASCADE;
DROP SERVER treasuredata_server;
DROP EXTENSION treasuredata_fdw CASCADE;