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 30select '[]'::fullname; -- bad 31select ' (Joe,Blow) '::fullname; -- ok, extra whitespace 32select '(Joe,Blow) /'::fullname; -- bad 33 34create temp table quadtable(f1 int, q quad); 35 36insert into quadtable values (1, ((3.3,4.4),(5.5,6.6))); 37insert into quadtable values (2, ((null,4.4),(5.5,6.6))); 38 39select * from quadtable; 40 41select f1, q.c1 from quadtable; -- fails, q is a table reference 42 43select f1, (q).c1, (qq.q).c1.i from quadtable qq; 44 45create temp table people (fn fullname, bd date); 46 47insert into people values ('(Joe,Blow)', '1984-01-10'); 48 49select * from people; 50 51-- at the moment this will not work due to ALTER TABLE inadequacy: 52alter table fullname add column suffix text default ''; 53 54-- but this should work: 55alter table fullname add column suffix text default null; 56 57select * from people; 58 59-- test insertion/updating of subfields 60update people set fn.suffix = 'Jr'; 61 62select * from people; 63 64insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66); 65 66select * from quadtable; 67 68-- The object here is to ensure that toasted references inside 69-- composite values don't cause problems. The large f1 value will 70-- be toasted inside pp, it must still work after being copied to people. 71 72create temp table pp (f1 text); 73insert into pp values (repeat('abcdefghijkl', 100000)); 74 75insert into people select ('Jim', f1, null)::fullname, current_date from pp; 76 77select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people; 78 79-- Test row comparison semantics. Prior to PG 8.2 we did this in a totally 80-- non-spec-compliant way. 81 82select ROW(1,2) < ROW(1,3) as true; 83select ROW(1,2) < ROW(1,1) as false; 84select ROW(1,2) < ROW(1,NULL) as null; 85select ROW(1,2,3) < ROW(1,3,NULL) as true; -- the NULL is not examined 86select ROW(11,'ABC') < ROW(11,'DEF') as true; 87select ROW(11,'ABC') > ROW(11,'DEF') as false; 88select ROW(12,'ABC') > ROW(11,'DEF') as true; 89 90-- = and <> have different NULL-behavior than < etc 91select ROW(1,2,3) < ROW(1,NULL,4) as null; 92select ROW(1,2,3) = ROW(1,NULL,4) as false; 93select ROW(1,2,3) <> ROW(1,NULL,4) as true; 94 95-- We allow operators beyond the six standard ones, if they have btree 96-- operator classes. 97select ROW('ABC','DEF') ~<=~ ROW('DEF','ABC') as true; 98select ROW('ABC','DEF') ~>=~ ROW('DEF','ABC') as false; 99select ROW('ABC','DEF') ~~ ROW('DEF','ABC') as fail; 100 101-- Comparisons of ROW() expressions can cope with some type mismatches 102select ROW(1,2) = ROW(1,2::int8); 103select ROW(1,2) in (ROW(3,4), ROW(1,2)); 104select ROW(1,2) in (ROW(3,4), ROW(1,2::int8)); 105 106-- Check row comparison with a subselect 107select unique1, unique2 from tenk1 108where (unique1, unique2) < any (select ten, ten from tenk1 where hundred < 3) 109 and unique1 <= 20 110order by 1; 111 112-- Also check row comparison with an indexable condition 113explain (costs off) 114select thousand, tenthous from tenk1 115where (thousand, tenthous) >= (997, 5000) 116order by thousand, tenthous; 117 118select thousand, tenthous from tenk1 119where (thousand, tenthous) >= (997, 5000) 120order by thousand, tenthous; 121 122explain (costs off) 123select thousand, tenthous, four from tenk1 124where (thousand, tenthous, four) > (998, 5000, 3) 125order by thousand, tenthous; 126 127select thousand, tenthous, four from tenk1 128where (thousand, tenthous, four) > (998, 5000, 3) 129order by thousand, tenthous; 130 131explain (costs off) 132select thousand, tenthous from tenk1 133where (998, 5000) < (thousand, tenthous) 134order by thousand, tenthous; 135 136select thousand, tenthous from tenk1 137where (998, 5000) < (thousand, tenthous) 138order by thousand, tenthous; 139 140explain (costs off) 141select thousand, hundred from tenk1 142where (998, 5000) < (thousand, hundred) 143order by thousand, hundred; 144 145select thousand, hundred from tenk1 146where (998, 5000) < (thousand, hundred) 147order by thousand, hundred; 148 149-- Test case for bug #14010: indexed row comparisons fail with nulls 150create temp table test_table (a text, b text); 151insert into test_table values ('a', 'b'); 152insert into test_table select 'a', null from generate_series(1,1000); 153insert into test_table values ('b', 'a'); 154create index on test_table (a,b); 155set enable_sort = off; 156 157explain (costs off) 158select a,b from test_table where (a,b) > ('a','a') order by a,b; 159 160select a,b from test_table where (a,b) > ('a','a') order by a,b; 161 162reset enable_sort; 163 164-- Check row comparisons with IN 165select * from int8_tbl i8 where i8 in (row(123,456)); -- fail, type mismatch 166 167explain (costs off) 168select * from int8_tbl i8 169where i8 in (row(123,456)::int8_tbl, '(4567890123456789,123)'); 170 171select * from int8_tbl i8 172where i8 in (row(123,456)::int8_tbl, '(4567890123456789,123)'); 173 174-- Check ability to select columns from an anonymous rowtype 175select (row(1, 2.0)).f1; 176select (row(1, 2.0)).f2; 177select (row(1, 2.0)).nosuch; -- fail 178select (row(1, 2.0)).*; 179select (r).f1 from (select row(1, 2.0) as r) ss; 180select (r).f3 from (select row(1, 2.0) as r) ss; -- fail 181select (r).* from (select row(1, 2.0) as r) ss; 182 183-- Check some corner cases involving empty rowtypes 184select ROW(); 185select ROW() IS NULL; 186select ROW() = ROW(); 187 188-- Check ability to create arrays of anonymous rowtypes 189select array[ row(1,2), row(3,4), row(5,6) ]; 190 191-- Check ability to compare an anonymous row to elements of an array 192select row(1,1.1) = any (array[ row(7,7.7), row(1,1.1), row(0,0.0) ]); 193select row(1,1.1) = any (array[ row(7,7.7), row(1,1.0), row(0,0.0) ]); 194 195-- Check behavior with a non-comparable rowtype 196create type cantcompare as (p point, r float8); 197create temp table cc (f1 cantcompare); 198insert into cc values('("(1,2)",3)'); 199insert into cc values('("(4,5)",6)'); 200select * from cc order by f1; -- fail, but should complain about cantcompare 201 202-- 203-- Tests for record_{eq,cmp} 204-- 205 206create type testtype1 as (a int, b int); 207 208-- all true 209select row(1, 2)::testtype1 < row(1, 3)::testtype1; 210select row(1, 2)::testtype1 <= row(1, 3)::testtype1; 211select row(1, 2)::testtype1 = row(1, 2)::testtype1; 212select row(1, 2)::testtype1 <> row(1, 3)::testtype1; 213select row(1, 3)::testtype1 >= row(1, 2)::testtype1; 214select row(1, 3)::testtype1 > row(1, 2)::testtype1; 215 216-- all false 217select row(1, -2)::testtype1 < row(1, -3)::testtype1; 218select row(1, -2)::testtype1 <= row(1, -3)::testtype1; 219select row(1, -2)::testtype1 = row(1, -3)::testtype1; 220select row(1, -2)::testtype1 <> row(1, -2)::testtype1; 221select row(1, -3)::testtype1 >= row(1, -2)::testtype1; 222select row(1, -3)::testtype1 > row(1, -2)::testtype1; 223 224-- true, but see *< below 225select row(1, -2)::testtype1 < row(1, 3)::testtype1; 226 227-- mismatches 228create type testtype3 as (a int, b text); 229select row(1, 2)::testtype1 < row(1, 'abc')::testtype3; 230select row(1, 2)::testtype1 <> row(1, 'abc')::testtype3; 231create type testtype5 as (a int); 232select row(1, 2)::testtype1 < row(1)::testtype5; 233select row(1, 2)::testtype1 <> row(1)::testtype5; 234 235-- non-comparable types 236create type testtype6 as (a int, b point); 237select row(1, '(1,2)')::testtype6 < row(1, '(1,3)')::testtype6; 238select row(1, '(1,2)')::testtype6 <> row(1, '(1,3)')::testtype6; 239 240drop type testtype1, testtype3, testtype5, testtype6; 241 242-- 243-- Tests for record_image_{eq,cmp} 244-- 245 246create type testtype1 as (a int, b int); 247 248-- all true 249select row(1, 2)::testtype1 *< row(1, 3)::testtype1; 250select row(1, 2)::testtype1 *<= row(1, 3)::testtype1; 251select row(1, 2)::testtype1 *= row(1, 2)::testtype1; 252select row(1, 2)::testtype1 *<> row(1, 3)::testtype1; 253select row(1, 3)::testtype1 *>= row(1, 2)::testtype1; 254select row(1, 3)::testtype1 *> row(1, 2)::testtype1; 255 256-- all false 257select row(1, -2)::testtype1 *< row(1, -3)::testtype1; 258select row(1, -2)::testtype1 *<= row(1, -3)::testtype1; 259select row(1, -2)::testtype1 *= row(1, -3)::testtype1; 260select row(1, -2)::testtype1 *<> row(1, -2)::testtype1; 261select row(1, -3)::testtype1 *>= row(1, -2)::testtype1; 262select row(1, -3)::testtype1 *> row(1, -2)::testtype1; 263 264-- This returns the "wrong" order because record_image_cmp works on 265-- unsigned datums without knowing about the actual data type. 266select row(1, -2)::testtype1 *< row(1, 3)::testtype1; 267 268-- other types 269create type testtype2 as (a smallint, b bool); -- byval different sizes 270select row(1, true)::testtype2 *< row(2, true)::testtype2; 271select row(-2, true)::testtype2 *< row(-1, true)::testtype2; 272select row(0, false)::testtype2 *< row(0, true)::testtype2; 273select row(0, false)::testtype2 *<> row(0, true)::testtype2; 274 275create type testtype3 as (a int, b text); -- variable length 276select row(1, 'abc')::testtype3 *< row(1, 'abd')::testtype3; 277select row(1, 'abc')::testtype3 *< row(1, 'abcd')::testtype3; 278select row(1, 'abc')::testtype3 *> row(1, 'abd')::testtype3; 279select row(1, 'abc')::testtype3 *<> row(1, 'abd')::testtype3; 280 281create type testtype4 as (a int, b point); -- by ref, fixed length 282select row(1, '(1,2)')::testtype4 *< row(1, '(1,3)')::testtype4; 283select row(1, '(1,2)')::testtype4 *<> row(1, '(1,3)')::testtype4; 284 285-- mismatches 286select row(1, 2)::testtype1 *< row(1, 'abc')::testtype3; 287select row(1, 2)::testtype1 *<> row(1, 'abc')::testtype3; 288create type testtype5 as (a int); 289select row(1, 2)::testtype1 *< row(1)::testtype5; 290select row(1, 2)::testtype1 *<> row(1)::testtype5; 291 292-- non-comparable types 293create type testtype6 as (a int, b point); 294select row(1, '(1,2)')::testtype6 *< row(1, '(1,3)')::testtype6; 295select row(1, '(1,2)')::testtype6 *>= row(1, '(1,3)')::testtype6; 296select row(1, '(1,2)')::testtype6 *<> row(1, '(1,3)')::testtype6; 297 298-- anonymous rowtypes in coldeflists 299select q.a, q.b = row(2), q.c = array[row(3)], q.d = row(row(4)) from 300 unnest(array[row(1, row(2), array[row(3)], row(row(4))), 301 row(2, row(3), array[row(4)], row(row(5)))]) 302 as q(a int, b record, c record[], d record); 303 304drop type testtype1, testtype2, testtype3, testtype4, testtype5, testtype6; 305 306 307-- 308-- Test case derived from bug #5716: check multiple uses of a rowtype result 309-- 310 311BEGIN; 312 313CREATE TABLE price ( 314 id SERIAL PRIMARY KEY, 315 active BOOLEAN NOT NULL, 316 price NUMERIC 317); 318 319CREATE TYPE price_input AS ( 320 id INTEGER, 321 price NUMERIC 322); 323 324CREATE TYPE price_key AS ( 325 id INTEGER 326); 327 328CREATE FUNCTION price_key_from_table(price) RETURNS price_key AS $$ 329 SELECT $1.id 330$$ LANGUAGE SQL; 331 332CREATE FUNCTION price_key_from_input(price_input) RETURNS price_key AS $$ 333 SELECT $1.id 334$$ LANGUAGE SQL; 335 336insert into price values (1,false,42), (10,false,100), (11,true,17.99); 337 338UPDATE price 339 SET active = true, price = input_prices.price 340 FROM unnest(ARRAY[(10, 123.00), (11, 99.99)]::price_input[]) input_prices 341 WHERE price_key_from_table(price.*) = price_key_from_input(input_prices.*); 342 343select * from price; 344 345rollback; 346 347-- 348-- Test case derived from bug #9085: check * qualification of composite 349-- parameters for SQL functions 350-- 351 352create temp table compos (f1 int, f2 text); 353 354create function fcompos1(v compos) returns void as $$ 355insert into compos values (v); -- fail 356$$ language sql; 357 358create function fcompos1(v compos) returns void as $$ 359insert into compos values (v.*); 360$$ language sql; 361 362create function fcompos2(v compos) returns void as $$ 363select fcompos1(v); 364$$ language sql; 365 366create function fcompos3(v compos) returns void as $$ 367select fcompos1(fcompos3.v.*); 368$$ language sql; 369 370select fcompos1(row(1,'one')); 371select fcompos2(row(2,'two')); 372select fcompos3(row(3,'three')); 373select * from compos; 374 375-- 376-- We allow I/O conversion casts from composite types to strings to be 377-- invoked via cast syntax, but not functional syntax. This is because 378-- the latter is too prone to be invoked unintentionally. 379-- 380select cast (fullname as text) from fullname; 381select fullname::text from fullname; 382select text(fullname) from fullname; -- error 383select fullname.text from fullname; -- error 384-- same, but RECORD instead of named composite type: 385select cast (row('Jim', 'Beam') as text); 386select (row('Jim', 'Beam'))::text; 387select text(row('Jim', 'Beam')); -- error 388select (row('Jim', 'Beam')).text; -- error 389 390-- 391-- Check the equivalence of functional and column notation 392-- 393insert into fullname values ('Joe', 'Blow'); 394 395select f.last from fullname f; 396select last(f) from fullname f; 397 398create function longname(fullname) returns text language sql 399as $$select $1.first || ' ' || $1.last$$; 400 401select f.longname from fullname f; 402select longname(f) from fullname f; 403 404-- Starting in v11, the notational form does matter if there's ambiguity 405alter table fullname add column longname text; 406 407select f.longname from fullname f; 408select longname(f) from fullname f; 409 410-- 411-- Test that composite values are seen to have the correct column names 412-- (bug #11210 and other reports) 413-- 414 415select row_to_json(i) from int8_tbl i; 416select row_to_json(i) from int8_tbl i(x,y); 417 418create temp view vv1 as select * from int8_tbl; 419select row_to_json(i) from vv1 i; 420select row_to_json(i) from vv1 i(x,y); 421 422select row_to_json(ss) from 423 (select q1, q2 from int8_tbl) as ss; 424select row_to_json(ss) from 425 (select q1, q2 from int8_tbl offset 0) as ss; 426select row_to_json(ss) from 427 (select q1 as a, q2 as b from int8_tbl) as ss; 428select row_to_json(ss) from 429 (select q1 as a, q2 as b from int8_tbl offset 0) as ss; 430select row_to_json(ss) from 431 (select q1 as a, q2 as b from int8_tbl) as ss(x,y); 432select row_to_json(ss) from 433 (select q1 as a, q2 as b from int8_tbl offset 0) as ss(x,y); 434 435explain (costs off) 436select row_to_json(q) from 437 (select thousand, tenthous from tenk1 438 where thousand = 42 and tenthous < 2000 offset 0) q; 439select row_to_json(q) from 440 (select thousand, tenthous from tenk1 441 where thousand = 42 and tenthous < 2000 offset 0) q; 442select row_to_json(q) from 443 (select thousand as x, tenthous as y from tenk1 444 where thousand = 42 and tenthous < 2000 offset 0) q; 445select row_to_json(q) from 446 (select thousand as x, tenthous as y from tenk1 447 where thousand = 42 and tenthous < 2000 offset 0) q(a,b); 448 449create temp table tt1 as select * from int8_tbl limit 2; 450create temp table tt2 () inherits(tt1); 451insert into tt2 values(0,0); 452select row_to_json(r) from (select q2,q1 from tt1 offset 0) r; 453 454-- check no-op rowtype conversions 455create temp table tt3 () inherits(tt2); 456insert into tt3 values(33,44); 457select row_to_json(tt3::tt2::tt1) from tt3; 458 459-- 460-- IS [NOT] NULL should not recurse into nested composites (bug #14235) 461-- 462 463explain (verbose, costs off) 464select r, r is null as isnull, r is not null as isnotnull 465from (values (1,row(1,2)), (1,row(null,null)), (1,null), 466 (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); 467 468select r, r is null as isnull, r is not null as isnotnull 469from (values (1,row(1,2)), (1,row(null,null)), (1,null), 470 (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); 471 472explain (verbose, costs off) 473with r(a,b) as materialized 474 (values (1,row(1,2)), (1,row(null,null)), (1,null), 475 (null,row(1,2)), (null,row(null,null)), (null,null) ) 476select r, r is null as isnull, r is not null as isnotnull from r; 477 478with r(a,b) as materialized 479 (values (1,row(1,2)), (1,row(null,null)), (1,null), 480 (null,row(1,2)), (null,row(null,null)), (null,null) ) 481select r, r is null as isnull, r is not null as isnotnull from r; 482 483 484-- 485-- Tests for component access / FieldSelect 486-- 487CREATE TABLE compositetable(a text, b text); 488INSERT INTO compositetable(a, b) VALUES('fa', 'fb'); 489 490-- composite type columns can't directly be accessed (error) 491SELECT d.a FROM (SELECT compositetable AS d FROM compositetable) s; 492-- but can be accessed with proper parens 493SELECT (d).a, (d).b FROM (SELECT compositetable AS d FROM compositetable) s; 494-- system columns can't be accessed in composite types (error) 495SELECT (d).ctid FROM (SELECT compositetable AS d FROM compositetable) s; 496 497-- accessing non-existing column in NULL datum errors out 498SELECT (NULL::compositetable).nonexistent; 499-- existing column in a NULL composite yield NULL 500SELECT (NULL::compositetable).a; 501-- oids can't be accessed in composite types (error) 502SELECT (NULL::compositetable).oid; 503 504DROP TABLE compositetable; 505