| -- |
| -- Test inheritance features |
| -- |
| CREATE TABLE a (dummy serial, aa TEXT); |
| CREATE TABLE b (bb TEXT) INHERITS (a); |
| CREATE TABLE c (cc TEXT) INHERITS (a); |
| CREATE TABLE d (dd TEXT) INHERITS (b,c,a); |
| |
| INSERT INTO a(aa) VALUES('aaa'); |
| INSERT INTO a(aa) VALUES('aaaa'); |
| INSERT INTO a(aa) VALUES('aaaaa'); |
| INSERT INTO a(aa) VALUES('aaaaaa'); |
| INSERT INTO a(aa) VALUES('aaaaaaa'); |
| INSERT INTO a(aa) VALUES('aaaaaaaa'); |
| |
| INSERT INTO b(aa) VALUES('bbb'); |
| INSERT INTO b(aa) VALUES('bbbb'); |
| INSERT INTO b(aa) VALUES('bbbbb'); |
| INSERT INTO b(aa) VALUES('bbbbbb'); |
| INSERT INTO b(aa) VALUES('bbbbbbb'); |
| INSERT INTO b(aa) VALUES('bbbbbbbb'); |
| |
| INSERT INTO c(aa) VALUES('ccc'); |
| INSERT INTO c(aa) VALUES('cccc'); |
| INSERT INTO c(aa) VALUES('ccccc'); |
| INSERT INTO c(aa) VALUES('cccccc'); |
| INSERT INTO c(aa) VALUES('ccccccc'); |
| INSERT INTO c(aa) VALUES('cccccccc'); |
| |
| INSERT INTO d(aa) VALUES('ddd'); |
| INSERT INTO d(aa) VALUES('dddd'); |
| INSERT INTO d(aa) VALUES('ddddd'); |
| INSERT INTO d(aa) VALUES('dddddd'); |
| INSERT INTO d(aa) VALUES('ddddddd'); |
| INSERT INTO d(aa) VALUES('dddddddd'); |
| |
| SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY 1,2; |
| |
| UPDATE a SET aa='zzzz' WHERE aa='aaaa'; |
| UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa'; |
| UPDATE b SET aa='zzz' WHERE aa='aaa'; |
| UPDATE ONLY b SET aa='zzz' WHERE aa='aaa'; |
| UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%'; |
| |
| SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY 1,2; |
| |
| UPDATE b SET aa='new'; |
| |
| SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY 1,2; |
| |
| UPDATE a SET aa='new'; |
| |
| DELETE FROM ONLY c WHERE aa='new'; |
| |
| SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY 1,2; |
| |
| DELETE FROM a; |
| |
| SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY 1,2; |
| SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY 1,2; |
| |
| -- Confirm PRIMARY KEY adds NOT NULL constraint to child table |
| CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a); |
| INSERT INTO z VALUES (NULL, 'text'); -- should fail |
| |
| -- Check UPDATE with inherited target and an inherited source table |
| create temp table foo(f1 int, f2 int); |
| create temp table foo2(f3 int) inherits (foo); |
| create temp table bar(f1 int, f2 int); |
| create temp table bar2(f3 int) inherits (bar); |
| |
| insert into foo values(1,1); |
| insert into foo values(3,3); |
| insert into foo2 values(2,2,2); |
| insert into foo2 values(3,3,3); |
| insert into bar values(1,1); |
| insert into bar values(2,2); |
| insert into bar values(3,3); |
| insert into bar values(4,4); |
| insert into bar2 values(1,1,1); |
| insert into bar2 values(2,2,2); |
| insert into bar2 values(3,3,3); |
| insert into bar2 values(4,4,4); |
| |
| update bar set f2 = f2 + 100 where f1 in (select f1 from foo); |
| |
| SELECT relname, bar.* FROM bar, pg_class where bar.tableoid = pg_class.oid |
| order by 1,2; |
| |
| |
| /* Test inheritance of structure (LIKE) */ |
| CREATE TABLE inhx (xx text DEFAULT 'text'); |
| |
| /* |
| * Test double inheritance |
| * |
| * Ensure that defaults are NOT included unless |
| * INCLUDING DEFAULTS is specified |
| */ |
| CREATE TABLE inhe (ee text, LIKE inhx) inherits (b); |
| INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', DEFAULT, 'ee-col4'); |
| SELECT * FROM inhe; /* Columns aa, bb, xx value NULL, ee */ |
| SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */ |
| SELECT * FROM b; /* Has ee entry */ |
| SELECT * FROM a; /* Has ee entry */ |
| |
| CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ |
| |
| CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS); |
| INSERT INTO inhf DEFAULT VALUES; |
| SELECT * FROM inhf; /* Single entry with value 'text' */ |
| |
| ALTER TABLE inhx add constraint foo CHECK (xx = 'text'); |
| ALTER TABLE inhx ADD PRIMARY KEY (xx); |
| CREATE TABLE inhg (LIKE inhx); /* Doesn't copy constraint */ |
| INSERT INTO inhg VALUES ('foo'); |
| DROP TABLE inhg; |
| CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */ |
| INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */ |
| INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */ |
| INSERT INTO inhg VALUES ('x', 'foo', 'y'); /* fails due to constraint */ |
| SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */ |
| DROP TABLE inhg; |
| |
| CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ |
| INSERT INTO inhg VALUES (5, 10); |
| INSERT INTO inhg VALUES (20, 10); -- should fail |
| DROP TABLE inhg; |
| /* Multiple primary keys creation should fail */ |
| CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */ |
| CREATE TABLE inhz (xx text DEFAULT 'text', yy int UNIQUE); |
| CREATE UNIQUE INDEX inhz_xx_idx on inhz (xx) WHERE xx <> 'test'; |
| /* Ok to create multiple unique indexes */ |
| CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES); |
| INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10); |
| INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15); |
| INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail |
| DROP TABLE inhg; |
| DROP TABLE inhz; |
| |
| -- Test changing the type of inherited columns |
| insert into d values('test','one','two','three'); |
| alter table a alter column aa type integer using bit_length(aa); |
| select * from d ORDER BY 1,2,3; |
| |
| -- Tests for casting between the rowtypes of parent and child |
| -- tables. See the pgsql-hackers thread beginning Dec. 4/04 |
| create table base (i integer); |
| create table derived () inherits (base); |
| insert into derived (i) values (0); |
| select derived::base from derived; |
| drop table derived; |
| drop table base; |
| |
| create table p1(ff1 int); |
| create table p2(f1 text); |
| create function p2text(p2) returns text as 'select $1.f1' language sql; |
| create table c1(f3 int) inherits(p1,p2); |
| insert into c1 values(123456789, 'hi', 42); |
| select p2text(c1.*) from c1; |
| drop function p2text(p2); |
| drop table c1; |
| drop table p2; |
| drop table p1; |