rowtypes.out

--
-- ROWTYPES
--
-- Make both a standalone composite type and a table rowtype
create type complex as (r float8, i float8);
create temp table fullname (first text, last text);
-- Nested composite
create type quad as (c1 complex, c2 complex);
-- Some simple tests of I/O conversions and row construction
select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad;
    row    |          row           
-----------+------------------------
 (1.1,2.2) | ("(3.3,4.4)","(5.5,)")
(1 row)

select row('Joe', 'Blow')::fullname, '(Joe,Blow)'::fullname;
    row     |  fullname  
------------+------------
 (Joe,Blow) | (Joe,Blow)
(1 row)

select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;
     fullname     |   fullname   
------------------+--------------
 (Joe,"von Blow") | (Joe,d'Blow)
(1 row)

select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname;
     fullname      |    fullname     
-------------------+-----------------
 (Joe,"von""Blow") | (Joe,"d\\Blow")
(1 row)

select '(Joe,"Blow,Jr")'::fullname;
    fullname     
-----------------
 (Joe,"Blow,Jr")
(1 row)

select '(Joe,)'::fullname;	-- ok, null 2nd column
 fullname 
----------
 (Joe,)
(1 row)

select '(Joe)'::fullname;	-- bad
ERROR:  malformed record literal: "(Joe)"
LINE 1: select '(Joe)'::fullname;
               ^
DETAIL:  Too few columns.
select '(Joe,,)'::fullname;	-- bad
ERROR:  malformed record literal: "(Joe,,)"
LINE 1: select '(Joe,,)'::fullname;
               ^
DETAIL:  Too many columns.
create temp table quadtable(f1 int, q quad);
insert into quadtable values (1, ((3.3,4.4),(5.5,6.6)));
insert into quadtable values (2, ((null,4.4),(5.5,6.6)));
select * from quadtable;
 f1 |             q             
----+---------------------------
  1 | ("(3.3,4.4)","(5.5,6.6)")
  2 | ("(,4.4)","(5.5,6.6)")
(2 rows)

select f1, q.c1 from quadtable;		-- fails, q is a table reference
ERROR:  missing FROM-clause entry for table "q"
LINE 1: select f1, q.c1 from quadtable;
                   ^
select f1, (q).c1, (qq.q).c1.i from quadtable qq;
 f1 |    c1     |  i  
----+-----------+-----
  1 | (3.3,4.4) | 4.4
  2 | (,4.4)    | 4.4
(2 rows)

create temp table people (fn fullname, bd date);
insert into people values ('(Joe,Blow)', '1984-01-10');
select * from people;
     fn     |     bd     
------------+------------
 (Joe,Blow) | 01-10-1984
(1 row)

-- at the moment this will not work due to ALTER TABLE inadequacy:
alter table fullname add column suffix text default '';
ERROR:  cannot alter table "fullname" because column "people"."fn" uses its rowtype
-- but this should work:
alter table fullname add column suffix text default null;
select * from people;
     fn      |     bd     
-------------+------------
 (Joe,Blow,) | 01-10-1984
(1 row)

-- test insertion/updating of subfields
update people set fn.suffix = 'Jr';
select * from people;
      fn       |     bd     
---------------+------------
 (Joe,Blow,Jr) | 01-10-1984
(1 row)

insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66);
select * from quadtable;
 f1 |             q             
----+---------------------------
  1 | ("(3.3,4.4)","(5.5,6.6)")
  2 | ("(,4.4)","(5.5,6.6)")
 44 | ("(55,)","(,66)")
(3 rows)

-- The object here is to ensure that toasted references inside
-- composite values don't cause problems.  The large f1 value will
-- be toasted inside pp, it must still work after being copied to people.
create temp table pp (f1 text);
insert into pp values (repeat('abcdefghijkl', 100000));
insert into people select ('Jim', f1, null)::fullname, current_date from pp;
select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people;
 first |        substr        | length  
-------+----------------------+---------
 Joe   | Blow                 |       4
 Jim   | abcdefghijklabcdefgh | 1200000
(2 rows)

-- Test row comparison semantics.  Prior to PG 8.2 we did this in a totally
-- non-spec-compliant way.
select ROW(1,2) < ROW(1,3) as true;
 true 
------
 t
(1 row)

select ROW(1,2) < ROW(1,1) as false;
 false 
-------
 f
(1 row)

select ROW(1,2) < ROW(1,NULL) as null;
 null 
------
 
(1 row)

select ROW(1,2,3) < ROW(1,3,NULL) as true; -- the NULL is not examined
 true 
------
 t
(1 row)

select ROW(11,'ABC') < ROW(11,'DEF') as true;
 true 
------
 t
(1 row)

select ROW(11,'ABC') > ROW(11,'DEF') as false;
 false 
-------
 f
(1 row)

select ROW(12,'ABC') > ROW(11,'DEF') as true;
 true 
------
 t
(1 row)

-- = and <> have different NULL-behavior than < etc
select ROW(1,2,3) < ROW(1,NULL,4) as null;
 null 
------
 
(1 row)

select ROW(1,2,3) = ROW(1,NULL,4) as false;
 false 
-------
 f
(1 row)

select ROW(1,2,3) <> ROW(1,NULL,4) as true;
 true 
------
 t
(1 row)

-- We allow operators beyond the six standard ones, if they have btree
-- operator classes.
select ROW('ABC','DEF') ~<=~ ROW('DEF','ABC') as true;
 true 
------
 t
(1 row)

select ROW('ABC','DEF') ~>=~ ROW('DEF','ABC') as false;
 false 
-------
 f
(1 row)

select ROW('ABC','DEF') ~~ ROW('DEF','ABC') as fail;
ERROR:  could not determine interpretation of row comparison operator ~~
LINE 1: select ROW('ABC','DEF') ~~ ROW('DEF','ABC') as fail;
                                ^
HINT:  Row comparison operators must be associated with btree operator families.
-- Check row comparison with a subselect
select unique1, unique2 from tenk1
where (unique1, unique2) < any (select ten, ten from tenk1 where hundred < 3)
      and unique1 <= 20
order by 1;
 unique1 | unique2 
---------+---------
       0 |    9998
       1 |    2838
(2 rows)

-- Also check row comparison with an indexable condition
select thousand, tenthous from tenk1
where (thousand, tenthous) >= (997, 5000)
order by thousand, tenthous;
 thousand | tenthous 
----------+----------
      997 |     5997
      997 |     6997
      997 |     7997
      997 |     8997
      997 |     9997
      998 |      998
      998 |     1998
      998 |     2998
      998 |     3998
      998 |     4998
      998 |     5998
      998 |     6998
      998 |     7998
      998 |     8998
      998 |     9998
      999 |      999
      999 |     1999
      999 |     2999
      999 |     3999
      999 |     4999
      999 |     5999
      999 |     6999
      999 |     7999
      999 |     8999
      999 |     9999
(25 rows)

-- Check some corner cases involving empty rowtypes
select ROW();
 row 
-----
 ()
(1 row)

select ROW() IS NULL;
 ?column? 
----------
 t
(1 row)

select ROW() = ROW();
ERROR:  cannot compare rows of zero length
LINE 1: select ROW() = ROW();
                     ^
-- Check ability to create arrays of anonymous rowtypes
select array[ row(1,2), row(3,4), row(5,6) ];
           array           
---------------------------
 {"(1,2)","(3,4)","(5,6)"}
(1 row)

-- Check ability to compare an anonymous row to elements of an array
select row(1,1.1) = any (array[ row(7,7.7), row(1,1.1), row(0,0.0) ]);
 ?column? 
----------
 t
(1 row)

select row(1,1.1) = any (array[ row(7,7.7), row(1,1.0), row(0,0.0) ]);
 ?column? 
----------
 f
(1 row)