tablefunc.out

PostgreSQL7.4.6 for Linux

--
-- first, define the functions.  Turn off echoing so that expected file
-- does not depend on contents of tablefunc.sql.
--
\set ECHO none
--
-- normal_rand()
-- no easy way to do this for regression testing
--
SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2);
 avg 
-----
 250
(1 row)

--
-- crosstab()
--
CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, val text);
\copy ct from 'data/ct.data'
SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
 row_name | category_1 | category_2 
----------+------------+------------
 test1    | val2       | val3
 test2    | val6       | val7
(2 rows)

SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
 row_name | category_1 | category_2 | category_3 
----------+------------+------------+------------
 test1    | val2       | val3       | 
 test2    | val6       | val7       | 
(2 rows)

SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
 row_name | category_1 | category_2 | category_3 | category_4 
----------+------------+------------+------------+------------
 test1    | val2       | val3       |            | 
 test2    | val6       | val7       |            | 
(2 rows)

SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
 row_name | category_1 | category_2 
----------+------------+------------
 test1    | val1       | val2
 test2    | val5       | val6
(2 rows)

SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
 row_name | category_1 | category_2 | category_3 
----------+------------+------------+------------
 test1    | val1       | val2       | val3
 test2    | val5       | val6       | val7
(2 rows)

SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
 row_name | category_1 | category_2 | category_3 | category_4 
----------+------------+------------+------------+------------
 test1    | val1       | val2       | val3       | val4
 test2    | val5       | val6       | val7       | val8
(2 rows)

SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
 row_name | category_1 | category_2 
----------+------------+------------
 test3    | val1       | val2
 test4    | val4       | val5
(2 rows)

SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
 row_name | category_1 | category_2 | category_3 
----------+------------+------------+------------
 test3    | val1       | val2       | 
 test4    | val4       | val5       | 
(2 rows)

SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
 row_name | category_1 | category_2 | category_3 | category_4 
----------+------------+------------+------------+------------
 test3    | val1       | val2       |            | 
 test4    | val4       | val5       |            | 
(2 rows)

SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
 row_name | category_1 | category_2 
----------+------------+------------
 test3    | val1       | val2
 test4    | val4       | val5
(2 rows)

SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
 row_name | category_1 | category_2 | category_3 
----------+------------+------------+------------
 test3    | val1       | val2       | val3
 test4    | val4       | val5       | val6
(2 rows)

SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
 row_name | category_1 | category_2 | category_3 | category_4 
----------+------------+------------+------------+------------
 test3    | val1       | val2       | val3       | 
 test4    | val4       | val5       | val6       | 
(2 rows)

SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 2) AS c(rowid text, att1 text, att2 text);
 rowid | att1 | att2 
-------+------+------
 test1 | val1 | val2
 test2 | val5 | val6
(2 rows)

SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 3) AS c(rowid text, att1 text, att2 text, att3 text);
 rowid | att1 | att2 | att3 
-------+------+------+------
 test1 | val1 | val2 | val3
 test2 | val5 | val6 | val7
(2 rows)

SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 4) AS c(rowid text, att1 text, att2 text, att3 text, att4 text);
 rowid | att1 | att2 | att3 | att4 
-------+------+------+------+------
 test1 | val1 | val2 | val3 | val4
 test2 | val5 | val6 | val7 | val8
(2 rows)

--
-- hash based crosstab
--
create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text);
NOTICE:  CREATE TABLE will create implicit sequence "cth_id_seq" for "serial" column "cth.id"
insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS');
-- the next line is intentionally left commented and is therefore a "missing" attribute
-- insert into cth values(DEFAULT,'test1','01 March 2003','test_startdate','28 February 2003');
insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987');
insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53');
insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
-- return attributes as plain text
SELECT * FROM crosstab(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
 rowid |          rowdt           | temperature | test_result | test_startdate | volts  
-------+--------------------------+-------------+-------------+----------------+--------
 test1 | Sat Mar 01 00:00:00 2003 | 42          | PASS        |                | 2.6987
 test2 | Sun Mar 02 00:00:00 2003 | 53          | FAIL        | 01 March 2003  | 3.1234
(2 rows)

-- this time without rowdt
SELECT * FROM crosstab(
  'SELECT rowid, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
 rowid | temperature | test_result | test_startdate | volts  
-------+-------------+-------------+----------------+--------
 test1 | 42          | PASS        |                | 2.6987
 test2 | 53          | FAIL        | 01 March 2003  | 3.1234
(2 rows)

-- convert attributes to specific datatypes
SELECT * FROM crosstab(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
 rowid |          rowdt           | temperature | test_result |      test_startdate      | volts