--

-- 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, '(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)"

DETAIL: Too few columns.

select '(Joe,,)'::fullname;-- bad

ERROR: malformed record literal: "(Joe,,)"

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: relation "q" does not exist

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)

-- This fails at the moment, would like it to work though:

update people set fn.suffix = 'Jr';

ERROR: syntax error at or near "." at character 21

LINE 1: update people set fn.suffix = 'Jr';

^

-- ugly workaround:

update people set fn = ((fn).first, (fn).last, 'III');

select * from people;

fn | bd

----------------+------------

(Joe,Blow,III) | 01-10-1984

(1 row)

-- 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)

# The sixth group of parallel test

# ----------

# "plpgsql" cannot run concurrently with "rules"

test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism

test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes

# run stats by itself because its delay may be insufficient under heavy load

test: stats

# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.24 2004/01/11 04:58:17 neilc Exp $

# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.25 2004/06/06 21:20:46 tgl Exp $

# This should probably be in an order similar to parallel_schedule.

test: boolean

test: char

test: alter_table

test: sequence

test: polymorphism

test: rowtypes

test: stats

createtypecomplexas (r float8, i float8);

create temp table fullname (firsttext, lasttext);

createtypequadas (c1 complex, c2 complex);

select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad;

select row('Joe','Blow')::fullname,'(Joe,Blow)'::fullname;

select'(Joe,von Blow)'::fullname,'(Joe,d''Blow)'::fullname;

select'(Joe,"von""Blow")'::fullname,'(Joe,d\\\\Blow)'::fullname;

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

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

select'(Joe)'::fullname;-- bad

select'(Joe,,)'::fullname;-- bad

create temp table quadtable(f1int, q quad);

insert into quadtablevalues (1, ((3.3,4.4),(5.5,6.6)));

insert into quadtablevalues (2, ((null,4.4),(5.5,6.6)));

select*from quadtable;

select f1,q.c1from quadtable;-- fails, q is a table reference

select f1, (q).c1, (qq.q).c1.ifrom quadtable qq;

create temp table people (fn fullname, bddate);

insert into peoplevalues ('(Joe,Blow)','1984-01-10');

select*from people;

altertable fullname add column suffixtext default'';

altertable fullname add column suffixtext defaultnull;

select*from people;

update peoplesetfn.suffix='Jr';

update peopleset fn= ((fn).first, (fn).last,'III');

select*from people;

create temp table pp (f1text);

insert into ppvalues (repeat('abcdefghijkl',100000));

insert into peopleselect ('Jim', f1,null)::fullname,current_datefrom pp;

select (fn).first, substr((fn).last,1,20), length((fn).last)from people;