src/ports/postgres/modules/utilities/test/utilities.sql_in

/* ----------------------------------------------------------------------- */
/**
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 */
/* ----------------------------------------------------------------------- */

m4_include(`SQLCommon.m4')

CREATE TABLE "__madlib_temp_Quoted"(b varchar);
CREATE TABLE __madlib_temp_non_quoted(a text);
-- assert that madlib_temp tables are created
SELECT assert(count(*) >= 2, 'Error setting up madlib_temp in schema ' || quote_ident(current_schema()))
FROM pg_tables
WHERE tablename LIKE '%madlib\_temp%'
  AND quote_ident(schemaname) = quote_ident(current_schema());

-- cleanup
SELECT cleanup_madlib_temp_tables(quote_ident(current_schema()));

-- assert that madlib_temp tables are dropped
SELECT assert(count(*) = 0, 'Error cleaning up madlib_temp in schema ' || quote_ident(current_schema()))
FROM pg_tables
WHERE tablename LIKE '%madlib\_temp%'
  AND quote_ident(schemaname) = quote_ident(current_schema());

-- test dropcols
DROP TABLE IF EXISTS dt_golf CASCADE;
CREATE TABLE dt_golf (
    id integer NOT NULL,
    id_2 integer,
    "OUTLOOK" text,
    temperature double precision,
    "len$$'%*()gth" double precision,
    "Cont,features" double precision[],
    cat_features text[],
    windy boolean,
    class text
)
m4_ifdef(`__POSTGRESQL__', `', `WITH (appendonly=true)');

INSERT INTO dt_golf (id,"OUTLOOK",temperature,"len$$'%*()gth","Cont,features",cat_features, windy,class) VALUES
(1, 'sunny', 85, 85,ARRAY[85, 85], ARRAY['a', 'b'], false, 'Don''t Play'),
(2, 'sunny', 80, 90, ARRAY[80, 90], ARRAY['a', 'b'], true, 'Don''t Play'),
(3, 'overcast', 83, 78, ARRAY[83, 78], ARRAY['a', 'b'], false, 'Play'),
(4, 'rain', 70, NULL, ARRAY[70, 96], ARRAY['a', 'b'], false, 'Play'),
(5, 'rain', 68, 80, ARRAY[68, 80], ARRAY['a', 'b'], false, 'Play'),
(6, 'rain', NULL, 70, ARRAY[65, 70], ARRAY['a', 'b'], true, 'Don''t Play'),
(7, 'overcast', 64, 65, ARRAY[64, 65], ARRAY['c', 'b'], NULL , 'Play'),
(8, 'sunny', 72, 95, ARRAY[72, 95], ARRAY['a', 'b'], false, 'Don''t Play'),
(9, 'sunny', 69, 70, ARRAY[69, 70], ARRAY['a', 'b'], false, 'Play'),
(10, 'rain', 75, 80, ARRAY[75, 80], ARRAY['a', 'b'], false, 'Play'),
(11, 'sunny', 75, 70, ARRAY[75, 70], ARRAY['a', 'd'], true, 'Play'),
(12, 'overcast', 72, 90, ARRAY[72, 90], ARRAY['c', 'b'], NULL, 'Play'),
(13, 'overcast', 81, 75, ARRAY[81, 75], ARRAY['a', 'b'], false, 'Play'),
(15, NULL, 81, 75, ARRAY[81, 75], ARRAY['a', 'b'], false, 'Play'),
(16, 'overcast', NULL, 75, ARRAY[81, 75], ARRAY['a', 'd'], false, 'Play'),
(14, 'rain', 71, 80, ARRAY[71, 80], ARRAY['c', 'b'], true, 'Don''t Play');

SELECT dropcols('dt_golf', 'dt_golf2', '"OUTLOOK", "Cont,features", cat_features');

-- test if columns have been dropped
SELECT assert(n_cols = 0, 'Some of the columns have not been dropped')
FROM (
    SELECT count(*) AS n_cols
    FROM information_schema.columns
    WHERE table_name='dt_golf2' AND
          (column_name in ('"OUTLOOK"', '"Cont,features"', 'cat_features'))
) q;

-- test if the retained columns are present in output table
SELECT
    id, id_2, temperature, "len$$'%*()gth", windy, class
FROM dt_golf2;