9 SET unique1 = onek.unique1 + 1;
12 SET unique1 = onek.unique1 - 1;
18 -- SET unique1 = onek2.unique1 + 1;
21 -- SET unique1 = onek2.unique1 - 1;
24 -- BTREE shutting out non-functional updates
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)
31 SET stringu1 = reverse_name(onek.stringu1)
33 WHERE onek.stringu1 = 'JBAAAA' and
34 onek.stringu1 = tmp.stringu1;
37 SET stringu1 = reverse_name(onek2.stringu1)
39 WHERE onek2.stringu1 = 'JCAAAA' and
40 onek2.stringu1 = tmp.stringu1;
49 -- WHERE name = 'linda';
54 COPY onek TO '@abs_builddir@/results/onek.data';
58 COPY onek FROM '@abs_builddir@/results/onek.data';
60 SELECT unique1 FROM onek WHERE unique1 < 2 ORDER BY unique1;
64 COPY onek2 FROM '@abs_builddir@/results/onek.data';
66 SELECT unique1 FROM onek2 WHERE unique1 < 2 ORDER BY unique1;
68 COPY BINARY stud_emp TO '@abs_builddir@/results/stud_emp.data';
72 COPY BINARY stud_emp FROM '@abs_builddir@/results/stud_emp.data';
74 SELECT * FROM stud_emp;
76 -- COPY aggtest FROM stdin;
82 -- COPY aggtest TO stdout;
86 -- inheritance stress test
88 SELECT * FROM a_star*;
92 WHERE x.b = text 'bumble' or x.a < 3;
96 WHERE x.c ~ text 'hi';
102 SELECT class, c FROM e_star* x WHERE x.c NOTNULL;
104 SELECT * FROM f_star* x WHERE x.c ISNULL;
106 -- grouping and aggregation on inherited sets have been busted in the past...
108 SELECT sum(a) FROM a_star*;
110 SELECT class, sum(a) FROM a_star* GROUP BY class ORDER BY class;
113 ALTER TABLE f_star RENAME COLUMN f TO ff;
115 ALTER TABLE e_star* RENAME COLUMN e TO ee;
117 ALTER TABLE d_star* RENAME COLUMN d TO dd;
119 ALTER TABLE c_star* RENAME COLUMN c TO cc;
121 ALTER TABLE b_star* RENAME COLUMN b TO bb;
123 ALTER TABLE a_star* RENAME COLUMN a TO aa;
129 -- As of Postgres 7.1, ALTER implicitly recurses,
130 -- so this should be same as ALTER a_star*
132 ALTER TABLE a_star RENAME COLUMN aa TO foo;
138 ALTER TABLE a_star RENAME COLUMN foo TO aa;
144 ALTER TABLE f_star ADD COLUMN f int4;
146 UPDATE f_star SET f = 10;
148 ALTER TABLE e_star* ADD COLUMN e int4;
150 --UPDATE e_star* SET e = 42;
152 SELECT * FROM e_star*;
154 ALTER TABLE a_star* ADD COLUMN a text;
156 -- That ALTER TABLE should have added TOAST tables.
157 SELECT relname, reltoastrelid <> 0 AS has_toast_table
159 WHERE oid::regclass IN ('a_star', 'c_star')
163 -- SET a = text 'gazpacho'
166 SELECT class, aa, a FROM a_star*;
174 -- postquel functions
177 -- mike does post_hacking,
178 -- joe and sally play basketball, and
179 -- everyone else does nothing.
181 SELECT p.name, name(p.hobbies) FROM ONLY person p;
184 -- as above, but jeff also does post_hacking.
186 SELECT p.name, name(p.hobbies) FROM person* p;
189 -- the next two queries demonstrate how functions generate bogus duplicates.
190 -- this is a "feature" ..
192 SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r
195 SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r;
198 -- mike needs advil and peet's coffee,
199 -- joe and sally need hightops, and
200 -- everyone else is fine.
202 SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p;
205 -- as above, but jeff needs advil and peet's coffee as well.
207 SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p;
210 -- just like the last two, but make sure that the target list fixup and
211 -- unflattening is being done correctly.
213 SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p;
215 SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p;
217 SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p;
219 SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p;
221 SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
223 SELECT name(equipment(hobby_construct_named(text 'skywalking', text 'mer')));
225 SELECT name(equipment_named(hobby_construct_named(text 'skywalking', text 'mer')));
227 SELECT name(equipment_named_ambiguous_1a(hobby_construct_named(text 'skywalking', text 'mer')));
229 SELECT name(equipment_named_ambiguous_1b(hobby_construct_named(text 'skywalking', text 'mer')));
231 SELECT name(equipment_named_ambiguous_1c(hobby_construct_named(text 'skywalking', text 'mer')));
233 SELECT name(equipment_named_ambiguous_2a(text 'skywalking'));
235 SELECT name(equipment_named_ambiguous_2b(text 'skywalking'));
237 SELECT hobbies_by_name('basketball');
239 SELECT name, overpaid(emp.*) FROM emp;
242 -- Try a few cases with SQL-spec row constructor expressions
244 SELECT * FROM equipment(ROW('skywalking', 'mer'));
246 SELECT name(equipment(ROW('skywalking', 'mer')));
248 SELECT *, name(equipment(h.*)) FROM hobbies_r h;
250 SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;