1-- 2-- ROWTYPES 3-- 4 5-- Make both a standalone composite type and a table rowtype 6 7create type complex as (r float8, i float8); 8 9create temp table fullname (first text, last text); 10 11-- Nested composite 12 13create type quad as (c1 complex, c2 complex); 14 15-- Some simple tests of I/O conversions and row construction 16 17select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad; 18 19select row('Joe', 'Blow')::fullname, '(Joe,Blow)'::fullname; 20 21select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname; 22 23select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname; 24 25select '(Joe,"Blow,Jr")'::fullname; 26 27select '(Joe,)'::fullname; -- ok, null 2nd column 28select '(Joe)'::fullname; -- bad 29select '(Joe,,)'::fullname; -- bad 30 31create temp table quadtable(f1 int, q quad); 32 33insert into quadtable values (1, ((3.3,4.4),(5.5,6.6))); 34insert into quadtable values (2, ((null,4.4),(5.5,6.6))); 35 36select * from quadtable; 37 38select f1, q.c1 from quadtable; -- fails, q is a table reference 39 40select f1, (q).c1, (qq.q).c1.i from quadtable qq; 41 42create temp table people (fn fullname, bd date); 43 44insert into people values ('(Joe,Blow)', '1984-01-10'); 45 46select * from people; 47 48-- at the moment this will not work due to ALTER TABLE inadequacy: 49alter table fullname add column suffix text default ''; 50 51-- but this should work: 52alter table fullname add column suffix text default null; 53 54select * from people; 55 56-- test insertion/updating of subfields 57update people set fn.suffix = 'Jr'; 58 59select * from people; 60 61insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66); 62 63select * from quadtable; 64 65-- The object here is to ensure that toasted references inside 66-- composite values don't cause problems. The large f1 value will 67-- be toasted inside pp, it must still work after being copied to people. 68 69create temp table pp (f1 text); 70insert into pp values (repeat('abcdefghijkl', 100000)); 71 72insert into people select ('Jim', f1, null)::fullname, current_date from pp; 73 74select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people; 75 76-- Test row comparison semantics. Prior to PG 8.2 we did this in a totally 77-- non-spec-compliant way. 78 79select ROW(1,2) < ROW(1,3) as true; 80select ROW(1,2) < ROW(1,1) as false; 81select ROW(1,2) < ROW(1,NULL) as null; 82select ROW(1,2,3) < ROW(1,3,NULL) as true; -- the NULL is not examined 83select ROW(11,'ABC') < ROW(11,'DEF') as true; 84select ROW(11,'ABC') > ROW(11,'DEF') as false; 85select ROW(12,'ABC') > ROW(11,'DEF') as true; 86 87-- = and <> have different NULL-behavior than < etc 88select ROW(1,2,3) < ROW(1,NULL,4) as null; 89select ROW(1,2,3) = ROW(1,NULL,4) as false; 90select ROW(1,2,3) <> ROW(1,NULL,4) as true; 91 92-- We allow operators beyond the six standard ones, if they have btree 93-- operator classes. 94select ROW('ABC','DEF') ~<=~ ROW('DEF','ABC') as true; 95select ROW('ABC','DEF') ~>=~ ROW('DEF','ABC') as false; 96select ROW('ABC','DEF') ~~ ROW('DEF','ABC') as fail; 97 98-- Comparisons of ROW() expressions can cope with some type mismatches 99select ROW(1,2) = ROW(1,2::int8); 100select ROW(1,2) in (ROW(3,4), ROW(1,2)); 101select ROW(1,2) in (ROW(3,4), ROW(1,2::int8)); 102 103-- Check row comparison with a subselect 104select unique1, unique2 from tenk1 105where (unique1, unique2) < any (select ten, ten from tenk1 where hundred < 3) 106 and unique1 <= 20 107order by 1; 108 109-- Also check row comparison with an indexable condition 110explain (costs off) 111select thousand, tenthous from tenk1 112where (thousand, tenthous) >= (997, 5000) 113order by thousand, tenthous; 114 115select thousand, tenthous from tenk1 116where (thousand, tenthous) >= (997, 5000) 117order by thousand, tenthous; 118 119-- Test case for bug #14010: indexed row comparisons fail with nulls 120create temp table test_table (a text, b text); 121insert into test_table values ('a', 'b'); 122insert into test_table select 'a', null from generate_series(1,1000); 123insert into test_table values ('b', 'a'); 124create index on test_table (a,b); 125set enable_sort = off; 126 127explain (costs off) 128select a,b from test_table where (a,b) > ('a','a') order by a,b; 129 130select a,b from test_table where (a,b) > ('a','a') order by a,b; 131 132reset enable_sort; 133 134-- Check row comparisons with IN 135select * from int8_tbl i8 where i8 in (row(123,456)); -- fail, type mismatch 136 137explain (costs off) 138select * from int8_tbl i8 139where i8 in (row(123,456)::int8_tbl, '(4567890123456789,123)'); 140 141select * from int8_tbl i8 142where i8 in (row(123,456)::int8_tbl, '(4567890123456789,123)'); 143 144-- Check some corner cases involving empty rowtypes 145select ROW(); 146select ROW() IS NULL; 147select ROW() = ROW(); 148 149-- Check ability to create arrays of anonymous rowtypes 150select array[ row(1,2), row(3,4), row(5,6) ]; 151 152-- Check ability to compare an anonymous row to elements of an array 153select row(1,1.1) = any (array[ row(7,7.7), row(1,1.1), row(0,0.0) ]); 154select row(1,1.1) = any (array[ row(7,7.7), row(1,1.0), row(0,0.0) ]); 155 156-- Check behavior with a non-comparable rowtype 157create type cantcompare as (p point, r float8); 158create temp table cc (f1 cantcompare); 159insert into cc values('("(1,2)",3)'); 160insert into cc values('("(4,5)",6)'); 161select * from cc order by f1; -- fail, but should complain about cantcompare 162 163-- 164-- Test case derived from bug #5716: check multiple uses of a rowtype result 165-- 166 167BEGIN; 168 169CREATE TABLE price ( 170 id SERIAL PRIMARY KEY, 171 active BOOLEAN NOT NULL, 172 price NUMERIC 173); 174 175CREATE TYPE price_input AS ( 176 id INTEGER, 177 price NUMERIC 178); 179 180CREATE TYPE price_key AS ( 181 id INTEGER 182); 183 184CREATE FUNCTION price_key_from_table(price) RETURNS price_key AS $$ 185 SELECT $1.id 186$$ LANGUAGE SQL; 187 188CREATE FUNCTION price_key_from_input(price_input) RETURNS price_key AS $$ 189 SELECT $1.id 190$$ LANGUAGE SQL; 191 192insert into price values (1,false,42), (10,false,100), (11,true,17.99); 193 194UPDATE price 195 SET active = true, price = input_prices.price 196 FROM unnest(ARRAY[(10, 123.00), (11, 99.99)]::price_input[]) input_prices 197 WHERE price_key_from_table(price.*) = price_key_from_input(input_prices.*); 198 199select * from price; 200 201rollback; 202 203-- 204-- Test case derived from bug #9085: check * qualification of composite 205-- parameters for SQL functions 206-- 207 208create temp table compos (f1 int, f2 text); 209 210create function fcompos1(v compos) returns void as $$ 211insert into compos values (v); -- fail 212$$ language sql; 213 214create function fcompos1(v compos) returns void as $$ 215insert into compos values (v.*); 216$$ language sql; 217 218create function fcompos2(v compos) returns void as $$ 219select fcompos1(v); 220$$ language sql; 221 222create function fcompos3(v compos) returns void as $$ 223select fcompos1(fcompos3.v.*); 224$$ language sql; 225 226select fcompos1(row(1,'one')); 227select fcompos2(row(2,'two')); 228select fcompos3(row(3,'three')); 229select * from compos; 230 231-- 232-- We allow I/O conversion casts from composite types to strings to be 233-- invoked via cast syntax, but not functional syntax. This is because 234-- the latter is too prone to be invoked unintentionally. 235-- 236select cast (fullname as text) from fullname; 237select fullname::text from fullname; 238select text(fullname) from fullname; -- error 239select fullname.text from fullname; -- error 240-- same, but RECORD instead of named composite type: 241select cast (row('Jim', 'Beam') as text); 242select (row('Jim', 'Beam'))::text; 243select text(row('Jim', 'Beam')); -- error 244select (row('Jim', 'Beam')).text; -- error 245 246-- 247-- Test that composite values are seen to have the correct column names 248-- (bug #11210 and other reports) 249-- 250 251select row_to_json(i) from int8_tbl i; 252select row_to_json(i) from int8_tbl i(x,y); 253 254create temp view vv1 as select * from int8_tbl; 255select row_to_json(i) from vv1 i; 256select row_to_json(i) from vv1 i(x,y); 257 258select row_to_json(ss) from 259 (select q1, q2 from int8_tbl) as ss; 260select row_to_json(ss) from 261 (select q1, q2 from int8_tbl offset 0) as ss; 262select row_to_json(ss) from 263 (select q1 as a, q2 as b from int8_tbl) as ss; 264select row_to_json(ss) from 265 (select q1 as a, q2 as b from int8_tbl offset 0) as ss; 266select row_to_json(ss) from 267 (select q1 as a, q2 as b from int8_tbl) as ss(x,y); 268select row_to_json(ss) from 269 (select q1 as a, q2 as b from int8_tbl offset 0) as ss(x,y); 270 271explain (costs off) 272select row_to_json(q) from 273 (select thousand, tenthous from tenk1 274 where thousand = 42 and tenthous < 2000 offset 0) q; 275select row_to_json(q) from 276 (select thousand, tenthous from tenk1 277 where thousand = 42 and tenthous < 2000 offset 0) q; 278select row_to_json(q) from 279 (select thousand as x, tenthous as y from tenk1 280 where thousand = 42 and tenthous < 2000 offset 0) q; 281select row_to_json(q) from 282 (select thousand as x, tenthous as y from tenk1 283 where thousand = 42 and tenthous < 2000 offset 0) q(a,b); 284 285create temp table tt1 as select * from int8_tbl limit 2; 286create temp table tt2 () inherits(tt1); 287insert into tt2 values(0,0); 288select row_to_json(r) from (select q2,q1 from tt1 offset 0) r; 289 290-- 291-- IS [NOT] NULL should not recurse into nested composites (bug #14235) 292-- 293 294explain (verbose, costs off) 295select r, r is null as isnull, r is not null as isnotnull 296from (values (1,row(1,2)), (1,row(null,null)), (1,null), 297 (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); 298 299select r, r is null as isnull, r is not null as isnotnull 300from (values (1,row(1,2)), (1,row(null,null)), (1,null), 301 (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); 302 303explain (verbose, costs off) 304with r(a,b) as 305 (values (1,row(1,2)), (1,row(null,null)), (1,null), 306 (null,row(1,2)), (null,row(null,null)), (null,null) ) 307select r, r is null as isnull, r is not null as isnotnull from r; 308 309with r(a,b) as 310 (values (1,row(1,2)), (1,row(null,null)), (1,null), 311 (null,row(1,2)), (null,row(null,null)), (null,null) ) 312select r, r is null as isnull, r is not null as isnotnull from r; 313