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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
SET search_path TO public, oracle;
-- Tests for the aggregate listagg
SELECT listagg(i::text) from generate_series(1,3) g(i);
listagg
---------
123
(1 row)
SELECT listagg(i::text, ',') from generate_series(1,3) g(i);
listagg
---------
1,2,3
(1 row)
SELECT coalesce(listagg(i::text), '<NULL>') from (SELECT ''::text) g(i);
coalesce
----------
(1 row)
SELECT coalesce(listagg(i::text), '<NULL>') from generate_series(1,0) g(i);
coalesce
----------
<NULL>
(1 row)
SELECT wm_concat(i::text) from generate_series(1,3) g(i);
wm_concat
-----------
1,2,3
(1 row)
-- 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();
checkmedianrealodd
--------------------
3800
(1 row)
SELECT checkMedianRealEven();
checkmedianrealeven
---------------------
2850
(1 row)
SELECT checkMedianDoubleOdd();
checkmediandoubleodd
----------------------
3600
(1 row)
SELECT checkMedianDoubleEven();
checkmediandoubleeven
-----------------------
2850
(1 row)
DROP FUNCTION checkMedianRealOdd();
DROP FUNCTION checkMedianRealEven();
DROP FUNCTION checkMedianDoubleOdd();
DROP FUNCTION checkMedianDoubleEven();
|
