| /* ----------------------------------------------------------------------- *//** |
| * |
| * 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. |
| * |
| *//* ----------------------------------------------------------------------- */ |
| |
| DROP TABLE IF EXISTS "PIVSET"; |
| |
| CREATE TABLE "PIVSET"( |
| id INTEGER, |
| piv INTEGER, |
| val INTEGER |
| ); |
| |
| INSERT INTO "PIVSET" VALUES |
| (0, 10, 1), |
| (0, 10, 2), |
| (0, 20, 3), |
| (1, 20, 4), |
| (1, 30, 5), |
| (1, 30, 6), |
| (1, 10, 7), |
| (NULL, 10, 8), |
| (0, NULL, 9), |
| (0, 10, NULL); |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('"PIVSET"', 'pivout', 'id', 'piv', 'val'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_avg_piv_20 = 3, 'Wrong output in pivoting') FROM pivout WHERE id = 0; |
| |
| DROP VIEW IF EXISTS pivset_ext; |
| CREATE VIEW pivset_ext AS |
| SELECT *, |
| COALESCE(id + ("PIVSET".val / 3), 0) AS id2, |
| COALESCE(piv + ("PIVSET".val / 3), 0) AS piv2, |
| COALESCE(val + 10, 0) AS val2 |
| FROM "PIVSET"; |
| SELECT id,id2,piv,piv2,val,val2 FROM pivset_ext |
| ORDER BY id,id2,piv,piv2,val,val2; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id,id2', 'piv', 'val'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_avg_piv_10 = 1.5, |
| 'Wrong output in pivoting: index columns') FROM pivout |
| WHERE id = 0 AND id2 = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_avg_piv_10_piv2_10 = 1.5, |
| 'Wrong output in pivoting: pivot columns') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val2_avg_piv_20 = 13, |
| 'Wrong output in pivoting: value columns') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_sum_piv_10 = 3, |
| 'Wrong output in pivoting: aggregate functions') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', True); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_sum_piv_null = 9, |
| 'Wrong output in pivoting: keep null') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', '111'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_sum_piv_30 = 111, |
| 'Wrong output in pivoting: fill value') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', '111', True); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_sum_piv_30 = 111 AND val_sum_piv_null = 9, |
| 'Wrong output in pivoting: fill value') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'avg, sum'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_avg_piv_10 = 1.5 AND val_sum_piv_10 = 3, |
| 'Wrong output in pivoting: aggregate functions') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val', 'avg, sum'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_avg_piv_10_piv2_10 = 1.5 AND val_sum_piv_10_piv2_10 = 3, |
| 'Wrong output in pivoting: aggregate functions') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2', 'avg, sum'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_sum_piv_10 = 3 AND val2_avg_piv_20 = 13, |
| 'Wrong output in pivoting: aggregate functions') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2', |
| 'val=avg, val2=sum'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val_avg_piv_10 = 1.5 AND val2_sum_piv_10 = 23, |
| 'Wrong output in pivoting: aggregate functions') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2', |
| 'val=avg, val2=[avg,sum]'); |
| SELECT * FROM pivout; |
| |
| SELECT assert(val2_avg_piv_20 = 13 AND val2_sum_piv_10 = 23, |
| 'Wrong output in pivoting: aggregate functions') FROM pivout WHERE id = 0; |
| |
| DROP TABLE IF EXISTS pivout; |
| DROP TABLE IF EXISTS pivout_dictionary; |
| SELECT pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2', |
| 'val=avg, val2=[avg,sum]', '111', True, True); |
| SELECT * FROM pivout; |
| |
| SELECT assert(__p_8__ = 1.5, |
| 'Wrong output in pivoting: Output dictionary') FROM pivout |
| WHERE id = 0 AND id2 = 0; |
| |
| DROP FUNCTION IF EXISTS array_add1(ANYARRAY, ANYELEMENT); |
| DROP AGGREGATE IF EXISTS array_accum1 (anyelement); |
| CREATE FUNCTION array_add1(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY AS $$ |
| SELECT $1 || $2 |
| $$ LANGUAGE sql STRICT; |
| |
| CREATE AGGREGATE array_accum1 (anyelement) ( |
| sfunc = array_add1, |
| stype = anyarray, |
| initcond = '{}' |
| ); |
| DROP TABLE IF EXISTS pivout; |
| SELECT pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'array_accum1'); |
| SELECT * FROM pivout; |
| |
| DROP TABLE IF EXISTS pivout; |
| DROP TABLE IF EXISTS pivout_dictionary; |
| SELECT pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2', |
| 'val=avg, val2=[avg,sum]', '111', True, True, 'a'); |
| SELECT * FROM pivout; |
| SELECT * FROM pivout_dictionary; |
| |
| DROP TABLE IF EXISTS pivout; |
| DROP TABLE IF EXISTS pivout_dictionary; |
| SELECT pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2', |
| 'val=avg, val2=[avg,sum]', '111', True, True, 's'); |
| SELECT * FROM pivout; |
| SELECT * FROM pivout_dictionary; |
| |
| DROP VIEW IF EXISTS pivset_ext; |