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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
--
-- Tests for PL/pgSQL handling of array variables
--
-- We also check arrays of composites here, so this has some overlap
-- with the plpgsql_record tests.
--
create type complex as (r float8, i float8);
create type quadarray as (c1 complex[], c2 complex);
do $$ declare a int[];
begin a := array[1,2]; a[3] := 4; raise notice 'a = %', a; end$$;
do $$ declare a int[];
begin a[3] := 4; raise notice 'a = %', a; end$$;
do $$ declare a int[];
begin a[1][4] := 4; raise notice 'a = %', a; end$$;
do $$ declare a int[];
begin a[1] := 23::text; raise notice 'a = %', a; end$$; -- lax typing
do $$ declare a int[];
begin a := array[1,2]; a[2:3] := array[3,4]; raise notice 'a = %', a; end$$;
do $$ declare a int[];
begin a := array[1,2]; a[2] := a[2] + 1; raise notice 'a = %', a; end$$;
do $$ declare a int[];
begin a[1:2] := array[3,4]; raise notice 'a = %', a; end$$;
do $$ declare a int[];
begin a[1:2] := 4; raise notice 'a = %', a; end$$; -- error
do $$ declare a complex[];
begin a[1] := (1,2); a[1].i := 11; raise notice 'a = %', a; end$$;
do $$ declare a complex[];
begin a[1].i := 11; raise notice 'a = %, a[1].i = %', a, a[1].i; end$$;
-- perhaps this ought to work, but for now it doesn't:
do $$ declare a complex[];
begin a[1:2].i := array[11,12]; raise notice 'a = %', a; end$$;
do $$ declare a quadarray;
begin a.c1[1].i := 11; raise notice 'a = %, a.c1[1].i = %', a, a.c1[1].i; end$$;
do $$ declare a int[];
begin a := array_agg(x) from (values(1),(2),(3)) v(x); raise notice 'a = %', a; end$$;
create temp table onecol as select array[1,2] as f1;
do $$ declare a int[];
begin a := f1 from onecol; raise notice 'a = %', a; end$$;
do $$ declare a int[];
begin a := * from onecol for update; raise notice 'a = %', a; end$$;
-- error cases:
do $$ declare a int[];
begin a := from onecol; raise notice 'a = %', a; end$$;
do $$ declare a int[];
begin a := f1, f1 from onecol; raise notice 'a = %', a; end$$;
insert into onecol values(array[11]);
do $$ declare a int[];
begin a := f1 from onecol; raise notice 'a = %', a; end$$;
do $$ declare a int[];
begin a := f1 from onecol limit 1; raise notice 'a = %', a; end$$;
do $$ declare a real;
begin a[1] := 2; raise notice 'a = %', a; end$$;
do $$ declare a complex;
begin a.r[1] := 2; raise notice 'a = %', a; end$$;
--
-- test of %type[] and %rowtype[] syntax
--
-- check supported syntax
do $$
declare
v int;
v1 v%type;
v2 v%type[];
v3 v%type[1];
v4 v%type[][];
v5 v%type[1][3];
v6 v%type array;
v7 v%type array[];
v8 v%type array[1];
v9 v%type array[1][1];
v10 pg_catalog.pg_class%rowtype[];
begin
raise notice '%', pg_typeof(v1);
raise notice '%', pg_typeof(v2);
raise notice '%', pg_typeof(v3);
raise notice '%', pg_typeof(v4);
raise notice '%', pg_typeof(v5);
raise notice '%', pg_typeof(v6);
raise notice '%', pg_typeof(v7);
raise notice '%', pg_typeof(v8);
raise notice '%', pg_typeof(v9);
raise notice '%', pg_typeof(v10);
end;
$$;
-- some types don't support arrays
do $$
declare
v pg_node_tree;
v1 v%type[];
begin
end;
$$;
-- check functionality
do $$
declare
v1 int;
v2 varchar;
a1 v1%type[];
a2 v2%type[];
begin
v1 := 10;
v2 := 'Hi';
a1 := array[v1,v1];
a2 := array[v2,v2];
raise notice '% %', a1, a2;
end;
$$;
create table array_test_table(a int, b varchar);
insert into array_test_table values(1, 'first'), (2, 'second');
do $$
declare tg array_test_table%rowtype[];
begin
tg := array(select array_test_table from array_test_table);
raise notice '%', tg;
tg := array(select row(a,b) from array_test_table);
raise notice '%', tg;
end;
$$;
|
