1-- 2-- MISC 3-- 4 5-- 6-- BTREE 7-- 8UPDATE onek 9 SET unique1 = onek.unique1 + 1; 10 11UPDATE onek 12 SET unique1 = onek.unique1 - 1; 13 14-- 15-- BTREE partial 16-- 17-- UPDATE onek2 18-- SET unique1 = onek2.unique1 + 1; 19 20--UPDATE onek2 21-- SET unique1 = onek2.unique1 - 1; 22 23-- 24-- BTREE shutting out non-functional updates 25-- 26-- the following two tests seem to take a long time on some 27-- systems. This non-func update stuff needs to be examined 28-- more closely. - jolly (2/22/96) 29-- 30UPDATE tmp 31 SET stringu1 = reverse_name(onek.stringu1) 32 FROM onek 33 WHERE onek.stringu1 = 'JBAAAA' and 34 onek.stringu1 = tmp.stringu1; 35 36UPDATE tmp 37 SET stringu1 = reverse_name(onek2.stringu1) 38 FROM onek2 39 WHERE onek2.stringu1 = 'JCAAAA' and 40 onek2.stringu1 = tmp.stringu1; 41 42DROP TABLE tmp; 43 44--UPDATE person* 45-- SET age = age + 1; 46 47--UPDATE person* 48-- SET age = age + 3 49-- WHERE name = 'linda'; 50 51-- 52-- copy 53-- 54COPY onek TO '@abs_builddir@/results/onek.data'; 55 56DELETE FROM onek; 57 58COPY onek FROM '@abs_builddir@/results/onek.data'; 59 60SELECT unique1 FROM onek WHERE unique1 < 2 ORDER BY unique1; 61 62DELETE FROM onek2; 63 64COPY onek2 FROM '@abs_builddir@/results/onek.data'; 65 66SELECT unique1 FROM onek2 WHERE unique1 < 2 ORDER BY unique1; 67 68COPY BINARY stud_emp TO '@abs_builddir@/results/stud_emp.data'; 69 70DELETE FROM stud_emp; 71 72COPY BINARY stud_emp FROM '@abs_builddir@/results/stud_emp.data'; 73 74SELECT * FROM stud_emp; 75 76-- COPY aggtest FROM stdin; 77-- 56 7.8 78-- 100 99.097 79-- 0 0.09561 80-- 42 324.78 81-- . 82-- COPY aggtest TO stdout; 83 84 85-- 86-- inheritance stress test 87-- 88SELECT * FROM a_star*; 89 90SELECT * 91 FROM b_star* x 92 WHERE x.b = text 'bumble' or x.a < 3; 93 94SELECT class, a 95 FROM c_star* x 96 WHERE x.c ~ text 'hi'; 97 98SELECT class, b, c 99 FROM d_star* x 100 WHERE x.a < 100; 101 102SELECT class, c FROM e_star* x WHERE x.c NOTNULL; 103 104SELECT * FROM f_star* x WHERE x.c ISNULL; 105 106-- grouping and aggregation on inherited sets have been busted in the past... 107 108SELECT sum(a) FROM a_star*; 109 110SELECT class, sum(a) FROM a_star* GROUP BY class ORDER BY class; 111 112 113ALTER TABLE f_star RENAME COLUMN f TO ff; 114 115ALTER TABLE e_star* RENAME COLUMN e TO ee; 116 117ALTER TABLE d_star* RENAME COLUMN d TO dd; 118 119ALTER TABLE c_star* RENAME COLUMN c TO cc; 120 121ALTER TABLE b_star* RENAME COLUMN b TO bb; 122 123ALTER TABLE a_star* RENAME COLUMN a TO aa; 124 125SELECT class, aa 126 FROM a_star* x 127 WHERE aa ISNULL; 128 129-- As of Postgres 7.1, ALTER implicitly recurses, 130-- so this should be same as ALTER a_star* 131 132ALTER TABLE a_star RENAME COLUMN aa TO foo; 133 134SELECT class, foo 135 FROM a_star* x 136 WHERE x.foo >= 2; 137 138ALTER TABLE a_star RENAME COLUMN foo TO aa; 139 140SELECT * 141 from a_star* 142 WHERE aa < 1000; 143 144ALTER TABLE f_star ADD COLUMN f int4; 145 146UPDATE f_star SET f = 10; 147 148ALTER TABLE e_star* ADD COLUMN e int4; 149 150--UPDATE e_star* SET e = 42; 151 152SELECT * FROM e_star*; 153 154ALTER TABLE a_star* ADD COLUMN a text; 155 156-- That ALTER TABLE should have added TOAST tables. 157SELECT relname, reltoastrelid <> 0 AS has_toast_table 158 FROM pg_class 159 WHERE oid::regclass IN ('a_star', 'c_star') 160 ORDER BY 1; 161 162--UPDATE b_star* 163-- SET a = text 'gazpacho' 164-- WHERE aa > 4; 165 166SELECT class, aa, a FROM a_star*; 167 168 169-- 170-- versions 171-- 172 173-- 174-- postquel functions 175-- 176-- 177-- mike does post_hacking, 178-- joe and sally play basketball, and 179-- everyone else does nothing. 180-- 181SELECT p.name, name(p.hobbies) FROM ONLY person p; 182 183-- 184-- as above, but jeff also does post_hacking. 185-- 186SELECT p.name, name(p.hobbies) FROM person* p; 187 188-- 189-- the next two queries demonstrate how functions generate bogus duplicates. 190-- this is a "feature" .. 191-- 192SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r 193 ORDER BY 1,2; 194 195SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r; 196 197-- 198-- mike needs advil and peet's coffee, 199-- joe and sally need hightops, and 200-- everyone else is fine. 201-- 202SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p; 203 204-- 205-- as above, but jeff needs advil and peet's coffee as well. 206-- 207SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p; 208 209-- 210-- just like the last two, but make sure that the target list fixup and 211-- unflattening is being done correctly. 212-- 213SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p; 214 215SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p; 216 217SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p; 218 219SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p; 220 221SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))); 222 223SELECT name(equipment(hobby_construct_named(text 'skywalking', text 'mer'))); 224 225SELECT name(equipment_named(hobby_construct_named(text 'skywalking', text 'mer'))); 226 227SELECT name(equipment_named_ambiguous_1a(hobby_construct_named(text 'skywalking', text 'mer'))); 228 229SELECT name(equipment_named_ambiguous_1b(hobby_construct_named(text 'skywalking', text 'mer'))); 230 231SELECT name(equipment_named_ambiguous_1c(hobby_construct_named(text 'skywalking', text 'mer'))); 232 233SELECT name(equipment_named_ambiguous_2a(text 'skywalking')); 234 235SELECT name(equipment_named_ambiguous_2b(text 'skywalking')); 236 237SELECT hobbies_by_name('basketball'); 238 239SELECT name, overpaid(emp.*) FROM emp; 240 241-- 242-- Try a few cases with SQL-spec row constructor expressions 243-- 244SELECT * FROM equipment(ROW('skywalking', 'mer')); 245 246SELECT name(equipment(ROW('skywalking', 'mer'))); 247 248SELECT *, name(equipment(h.*)) FROM hobbies_r h; 249 250SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h; 251 252-- 253-- functional joins 254-- 255 256-- 257-- instance rules 258-- 259 260-- 261-- rewrite rules 262-- 263