1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
SET search_path TO public, oracle;
-- Tests for the aggregate listagg
SELECT listagg(i::text) from generate_series(1,3) g(i);
SELECT listagg(i::text, ',') from generate_series(1,3) g(i);
SELECT coalesce(listagg(i::text), '<NULL>') from (SELECT ''::text) g(i);
SELECT coalesce(listagg(i::text), '<NULL>') from generate_series(1,0) g(i);
SELECT wm_concat(i::text) from generate_series(1,3) g(i);
-- Tests for the aggregate median( real | double )
CREATE FUNCTION checkMedianRealOdd() RETURNS real AS $$
DECLARE
med real;
BEGIN
CREATE TABLE median_test (salary real);
INSERT INTO median_test VALUES (4500);
INSERT INTO median_test VALUES (NULL);
INSERT INTO median_test VALUES (2100);
INSERT INTO median_test VALUES (3600);
INSERT INTO median_test VALUES (4000);
SELECT into med median(salary) from median_test;
DROP TABLE median_test;
return med;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION checkMedianRealEven() RETURNS real AS $$
DECLARE
med real;
BEGIN
CREATE TABLE median_test (salary real);
INSERT INTO median_test VALUES (4500);
INSERT INTO median_test VALUES (1500);
INSERT INTO median_test VALUES (2100);
INSERT INTO median_test VALUES (3600);
INSERT INTO median_test VALUES (1000);
INSERT INTO median_test VALUES (4000);
select into med median(salary) from median_test;
DROP TABLE median_test;
return med;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION checkMedianDoubleOdd() RETURNS double precision AS $$
DECLARE
med double precision;
BEGIN
CREATE TABLE median_test (salary double precision);
INSERT INTO median_test VALUES (4500);
INSERT INTO median_test VALUES (1500);
INSERT INTO median_test VALUES (2100);
INSERT INTO median_test VALUES (3600);
INSERT INTO median_test VALUES (4000);
select into med median(salary) from median_test;
DROP TABLE median_test;
return med;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION checkMedianDoubleEven() RETURNS double precision AS $$
DECLARE
med double precision;
BEGIN
CREATE TABLE median_test (salary double precision);
INSERT INTO median_test VALUES (4500);
INSERT INTO median_test VALUES (1500);
INSERT INTO median_test VALUES (2100);
INSERT INTO median_test VALUES (3600);
INSERT INTO median_test VALUES (4000);
INSERT INTO median_test VALUES (1000);
select into med median(salary) from median_test;
DROP TABLE median_test;
return med;
END;
$$ LANGUAGE plpgsql;
SELECT checkMedianRealOdd();
SELECT checkMedianRealEven();
SELECT checkMedianDoubleOdd();
SELECT checkMedianDoubleEven();
DROP FUNCTION checkMedianRealOdd();
DROP FUNCTION checkMedianRealEven();
DROP FUNCTION checkMedianDoubleOdd();
DROP FUNCTION checkMedianDoubleEven();
|
