4
-- Make both a standalone composite type and a table rowtype
5
create type complex as (r float8, i float8);
6
create temp table fullname (first text, last text);
8
create type quad as (c1 complex, c2 complex);
9
-- Some simple tests of I/O conversions and row construction
10
select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad;
12
-----------+------------------------
13
(1.1,2.2) | ("(3.3,4.4)","(5.5,)")
16
select row('Joe', 'Blow')::fullname, '(Joe,Blow)'::fullname;
18
------------+------------
19
(Joe,Blow) | (Joe,Blow)
22
select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;
24
------------------+--------------
25
(Joe,"von Blow") | (Joe,d'Blow)
28
select '(Joe,"von""Blow")'::fullname, '(Joe,d\\\\Blow)'::fullname;
30
-------------------+-----------------
31
(Joe,"von""Blow") | (Joe,"d\\Blow")
34
select '(Joe,"Blow,Jr")'::fullname;
40
select '(Joe,)'::fullname; -- ok, null 2nd column
46
select '(Joe)'::fullname; -- bad
47
ERROR: malformed record literal: "(Joe)"
48
DETAIL: Too few columns.
49
select '(Joe,,)'::fullname; -- bad
50
ERROR: malformed record literal: "(Joe,,)"
51
DETAIL: Too many columns.
52
create temp table quadtable(f1 int, q quad);
53
insert into quadtable values (1, ((3.3,4.4),(5.5,6.6)));
54
insert into quadtable values (2, ((null,4.4),(5.5,6.6)));
55
select * from quadtable;
57
----+---------------------------
58
1 | ("(3.3,4.4)","(5.5,6.6)")
59
2 | ("(,4.4)","(5.5,6.6)")
62
select f1, q.c1 from quadtable; -- fails, q is a table reference
63
ERROR: relation "q" does not exist
64
select f1, (q).c1, (qq.q).c1.i from quadtable qq;
66
----+-----------+-----
71
create temp table people (fn fullname, bd date);
72
insert into people values ('(Joe,Blow)', '1984-01-10');
75
------------+------------
76
(Joe,Blow) | 01-10-1984
79
-- at the moment this will not work due to ALTER TABLE inadequacy:
80
alter table fullname add column suffix text default '';
81
ERROR: cannot alter table "fullname" because column "people"."fn" uses its rowtype
82
-- but this should work:
83
alter table fullname add column suffix text default null;
86
-------------+------------
87
(Joe,Blow,) | 01-10-1984
90
-- test insertion/updating of subfields
91
update people set fn.suffix = 'Jr';
94
---------------+------------
95
(Joe,Blow,Jr) | 01-10-1984
98
insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66);
99
select * from quadtable;
101
----+---------------------------
102
1 | ("(3.3,4.4)","(5.5,6.6)")
103
2 | ("(,4.4)","(5.5,6.6)")
104
44 | ("(55,)","(,66)")
107
-- The object here is to ensure that toasted references inside
108
-- composite values don't cause problems. The large f1 value will
109
-- be toasted inside pp, it must still work after being copied to people.
110
create temp table pp (f1 text);
111
insert into pp values (repeat('abcdefghijkl', 100000));
112
insert into people select ('Jim', f1, null)::fullname, current_date from pp;
113
select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people;
114
first | substr | length
115
-------+----------------------+---------
117
Jim | abcdefghijklabcdefgh | 1200000