| blob | 91e0d2b04d79dab77c3dab10c274e7a496c856f1 |
1 --
2 -- MISC
3 --
4 --
5 -- BTREE
6 --
7 UPDATE onek
8 SET unique1 = onek.unique1 + 1;
9 UPDATE onek
10 SET unique1 = onek.unique1 - 1;
11 --
12 -- BTREE partial
13 --
14 -- UPDATE onek2
15 -- SET unique1 = onek2.unique1 + 1;
16 --UPDATE onek2
17 -- SET unique1 = onek2.unique1 - 1;
18 --
19 -- BTREE shutting out non-functional updates
20 --
21 -- the following two tests seem to take a long time on some
22 -- systems. This non-func update stuff needs to be examined
23 -- more closely. - jolly (2/22/96)
24 --
25 UPDATE tmp
26 SET stringu1 = reverse_name(onek.stringu1)
27 FROM onek
28 WHERE onek.stringu1 = 'JBAAAA' and
29 onek.stringu1 = tmp.stringu1;
30 UPDATE tmp
31 SET stringu1 = reverse_name(onek2.stringu1)
32 FROM onek2
33 WHERE onek2.stringu1 = 'JCAAAA' and
34 onek2.stringu1 = tmp.stringu1;
35 DROP TABLE tmp;
36 --UPDATE person*
37 -- SET age = age + 1;
38 --UPDATE person*
39 -- SET age = age + 3
40 -- WHERE name = 'linda';
41 --
42 -- copy
43 --
44 COPY onek TO '@abs_builddir@/results/onek.data';
45 DELETE FROM onek;
46 COPY onek FROM '@abs_builddir@/results/onek.data';
47 SELECT unique1 FROM onek WHERE unique1 < 2 ORDER BY unique1;
48 unique1
49 ---------
50 0
51 1
52 (2 rows)
54 DELETE FROM onek2;
55 COPY onek2 FROM '@abs_builddir@/results/onek.data';
56 SELECT unique1 FROM onek2 WHERE unique1 < 2 ORDER BY unique1;
57 unique1
58 ---------
59 0
60 1
61 (2 rows)
63 COPY BINARY stud_emp TO '@abs_builddir@/results/stud_emp.data';
64 DELETE FROM stud_emp;
65 COPY BINARY stud_emp FROM '@abs_builddir@/results/stud_emp.data';
66 SELECT * FROM stud_emp;
67 name | age | location | salary | manager | gpa | percent
68 -------+-----+------------+--------+---------+-----+---------
69 jeff | 23 | (8,7.7) | 600 | sharon | 3.5 |
70 cim | 30 | (10.5,4.7) | 400 | | 3.4 |
71 linda | 19 | (0.9,6.1) | 100 | | 2.9 |
72 (3 rows)
74 -- COPY aggtest FROM stdin;
75 -- 56 7.8
76 -- 100 99.097
77 -- 0 0.09561
78 -- 42 324.78
79 -- .
80 -- COPY aggtest TO stdout;
81 --
82 -- inheritance stress test
83 --
84 SELECT * FROM a_star*;
85 class | a
86 -------+----
87 a | 1
88 a | 2
89 a |
90 b | 3
91 b | 4
92 b |
93 b |
94 c | 5
95 c | 6
96 c |
97 c |
98 d | 7
99 d | 8
100 d | 9
101 d | 10
102 d |
103 d | 11
104 d | 12
105 d | 13
106 d |
107 d |
108 d |
109 d | 14
110 d |
111 d |
112 d |
113 d |
114 e | 15
115 e | 16
116 e | 17
117 e |
118 e | 18
119 e |
120 e |
121 f | 19
122 f | 20
123 f | 21
124 f | 22
125 f |
126 f | 24
127 f | 25
128 f | 26
129 f |
130 f |
131 f |
132 f | 27
133 f |
134 f |
135 f |
136 f |
137 (50 rows)
139 SELECT *
140 FROM b_star* x
141 WHERE x.b = text 'bumble' or x.a < 3;
142 class | a | b
143 -------+---+--------
144 b | | bumble
145 (1 row)
147 SELECT class, a
148 FROM c_star* x
149 WHERE x.c ~ text 'hi';
150 class | a
151 -------+----
152 c | 5
153 c |
154 d | 7
155 d | 8
156 d | 10
157 d |
158 d | 12
159 d |
160 d |
161 d |
162 e | 15
163 e | 16
164 e |
165 e |
166 f | 19
167 f | 20
168 f | 21
169 f |
170 f | 24
171 f |
172 f |
173 f |
174 (22 rows)
176 SELECT class, b, c
177 FROM d_star* x
178 WHERE x.a < 100;
179 class | b | c
180 -------+---------+------------
181 d | grumble | hi sunita
182 d | stumble | hi koko
183 d | rumble |
184 d | | hi kristin
185 d | fumble |
186 d | | hi avi
187 d | |
188 d | |
189 (8 rows)
191 SELECT class, c FROM e_star* x WHERE x.c NOTNULL;
192 class | c
193 -------+-------------
194 e | hi carol
195 e | hi bob
196 e | hi michelle
197 e | hi elisa
198 f | hi claire
199 f | hi mike
200 f | hi marcel
201 f | hi keith
202 f | hi marc
203 f | hi allison
204 f | hi jeff
205 f | hi carl
206 (12 rows)
208 SELECT * FROM f_star* x WHERE x.c ISNULL;
209 class | a | c | e | f
210 -------+----+---+-----+-------------------------------------------
211 f | 22 | | -7 | ((111,555),(222,666),(333,777),(444,888))
212 f | 25 | | -9 |
213 f | 26 | | | ((11111,33333),(22222,44444))
214 f | | | -11 | ((1111111,3333333),(2222222,4444444))
215 f | 27 | | |
216 f | | | -12 |
217 f | | | | ((11111111,33333333),(22222222,44444444))
218 f | | | |
219 (8 rows)
221 -- grouping and aggregation on inherited sets have been busted in the past...
222 SELECT sum(a) FROM a_star*;
223 sum
224 -----
225 355
226 (1 row)
228 SELECT class, sum(a) FROM a_star* GROUP BY class ORDER BY class;
229 class | sum
230 -------+-----
231 a | 3
232 b | 7
233 c | 11
234 d | 84
235 e | 66
236 f | 184
237 (6 rows)
239 ALTER TABLE f_star RENAME COLUMN f TO ff;
240 ALTER TABLE e_star* RENAME COLUMN e TO ee;
241 ALTER TABLE d_star* RENAME COLUMN d TO dd;
242 ALTER TABLE c_star* RENAME COLUMN c TO cc;
243 ALTER TABLE b_star* RENAME COLUMN b TO bb;
244 ALTER TABLE a_star* RENAME COLUMN a TO aa;
245 SELECT class, aa
246 FROM a_star* x
247 WHERE aa ISNULL;
248 class | aa
249 -------+----
250 a |
251 b |
252 b |
253 c |
254 c |
255 d |
256 d |
257 d |
258 d |
259 d |
260 d |
261 d |
262 d |
263 e |
264 e |
265 e |
266 f |
267 f |
268 f |
269 f |
270 f |
271 f |
272 f |
273 f |
274 (24 rows)
276 -- As of Postgres 7.1, ALTER implicitly recurses,
277 -- so this should be same as ALTER a_star*
278 ALTER TABLE a_star RENAME COLUMN aa TO foo;
279 SELECT class, foo
280 FROM a_star* x
281 WHERE x.foo >= 2;
282 class | foo
283 -------+-----
284 a | 2
285 b | 3
286 b | 4
287 c | 5
288 c | 6
289 d | 7
290 d | 8
291 d | 9
292 d | 10
293 d | 11
294 d | 12
295 d | 13
296 d | 14
297 e | 15
298 e | 16
299 e | 17
300 e | 18
301 f | 19
302 f | 20
303 f | 21
304 f | 22
305 f | 24
306 f | 25
307 f | 26
308 f | 27
309 (25 rows)
311 ALTER TABLE a_star RENAME COLUMN foo TO aa;
312 SELECT *
313 from a_star*
314 WHERE aa < 1000;
315 class | aa
316 -------+----
317 a | 1
318 a | 2
319 b | 3
320 b | 4
321 c | 5
322 c | 6
323 d | 7
324 d | 8
325 d | 9
326 d | 10
327 d | 11
328 d | 12
329 d | 13
330 d | 14
331 e | 15
332 e | 16
333 e | 17
334 e | 18
335 f | 19
336 f | 20
337 f | 21
338 f | 22
339 f | 24
340 f | 25
341 f | 26
342 f | 27
343 (26 rows)
345 ALTER TABLE f_star ADD COLUMN f int4;
346 UPDATE f_star SET f = 10;
347 ALTER TABLE e_star* ADD COLUMN e int4;
348 --UPDATE e_star* SET e = 42;
349 SELECT * FROM e_star*;
350 class | aa | cc | ee | e
351 -------+----+-------------+-----+---
352 e | 15 | hi carol | -1 |
353 e | 16 | hi bob | |
354 e | 17 | | -2 |
355 e | | hi michelle | -3 |
356 e | 18 | | |
357 e | | hi elisa | |
358 e | | | -4 |
359 f | 19 | hi claire | -5 |
360 f | 20 | hi mike | -6 |
361 f | 21 | hi marcel | |
362 f | 22 | | -7 |
363 f | | hi keith | -8 |
364 f | 24 | hi marc | |
365 f | 25 | | -9 |
366 f | 26 | | |
367 f | | hi allison | -10 |
368 f | | hi jeff | |
369 f | | | -11 |
370 f | 27 | | |
371 f | | hi carl | |
372 f | | | -12 |
373 f | | | |
374 f | | | |
375 (23 rows)
377 ALTER TABLE a_star* ADD COLUMN a text;
378 NOTICE: merging definition of column "a" for child "d_star"
379 --UPDATE b_star*
380 -- SET a = text 'gazpacho'
381 -- WHERE aa > 4;
382 SELECT class, aa, a FROM a_star*;
383 class | aa | a
384 -------+----+---
385 a | 1 |
386 a | 2 |
387 a | |
388 b | 3 |
389 b | 4 |
390 b | |
391 b | |
392 c | 5 |
393 c | 6 |
394 c | |
395 c | |
396 d | 7 |
397 d | 8 |
398 d | 9 |
399 d | 10 |
400 d | |
401 d | 11 |
402 d | 12 |
403 d | 13 |
404 d | |
405 d | |
406 d | |
407 d | 14 |
408 d | |
409 d | |
410 d | |
411 d | |
412 e | 15 |
413 e | 16 |
414 e | 17 |
415 e | |
416 e | 18 |
417 e | |
418 e | |
419 f | 19 |
420 f | 20 |
421 f | 21 |
422 f | 22 |
423 f | |
424 f | 24 |
425 f | 25 |
426 f | 26 |
427 f | |
428 f | |
429 f | |
430 f | 27 |
431 f | |
432 f | |
433 f | |
434 f | |
435 (50 rows)
437 --
438 -- versions
439 --
440 --
441 -- postquel functions
442 --
443 --
444 -- mike does post_hacking,
445 -- joe and sally play basketball, and
446 -- everyone else does nothing.
447 --
448 SELECT p.name, name(p.hobbies) FROM ONLY person p;
449 name | name
450 -------+-------------
451 mike | posthacking
452 joe | basketball
453 sally | basketball
454 (3 rows)
456 --
457 -- as above, but jeff also does post_hacking.
458 --
459 SELECT p.name, name(p.hobbies) FROM person* p;
460 name | name
461 -------+-------------
462 mike | posthacking
463 joe | basketball
464 sally | basketball
465 jeff | posthacking
466 (4 rows)
468 --
469 -- the next two queries demonstrate how functions generate bogus duplicates.
470 -- this is a "feature" ..
471 --
472 SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r
473 ORDER BY 1,2;
474 name | name
475 -------------+---------------
476 basketball | hightops
477 posthacking | advil
478 posthacking | peet's coffee
479 skywalking | guts
480 (4 rows)
482 SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r;
483 name | name
484 -------------+---------------
485 posthacking | advil
486 posthacking | peet's coffee
487 posthacking | advil
488 posthacking | peet's coffee
489 basketball | hightops
490 basketball | hightops
491 skywalking | guts
492 (7 rows)
494 --
495 -- mike needs advil and peet's coffee,
496 -- joe and sally need hightops, and
497 -- everyone else is fine.
498 --
499 SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p;
500 name | name | name
501 -------+-------------+---------------
502 mike | posthacking | advil
503 mike | posthacking | peet's coffee
504 joe | basketball | hightops
505 sally | basketball | hightops
506 (4 rows)
508 --
509 -- as above, but jeff needs advil and peet's coffee as well.
510 --
511 SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p;
512 name | name | name
513 -------+-------------+---------------
514 mike | posthacking | advil
515 mike | posthacking | peet's coffee
516 joe | basketball | hightops
517 sally | basketball | hightops
518 jeff | posthacking | advil
519 jeff | posthacking | peet's coffee
520 (6 rows)
522 --
523 -- just like the last two, but make sure that the target list fixup and
524 -- unflattening is being done correctly.
525 --
526 SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p;
527 name | name | name
528 ---------------+-------+-------------
529 advil | mike | posthacking
530 peet's coffee | mike | posthacking
531 hightops | joe | basketball
532 hightops | sally | basketball
533 (4 rows)
535 SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p;
536 name | name | name
537 ---------------+-------+-------------
538 advil | mike | posthacking
539 peet's coffee | mike | posthacking
540 hightops | joe | basketball
541 hightops | sally | basketball
542 advil | jeff | posthacking
543 peet's coffee | jeff | posthacking
544 (6 rows)
546 SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p;
547 name | name | name
548 ---------------+-------------+-------
549 advil | posthacking | mike
550 peet's coffee | posthacking | mike
551 hightops | basketball | joe
552 hightops | basketball | sally
553 (4 rows)
555 SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p;
556 name | name | name
557 ---------------+-------------+-------
558 advil | posthacking | mike
559 peet's coffee | posthacking | mike
560 hightops | basketball | joe
561 hightops | basketball | sally
562 advil | posthacking | jeff
563 peet's coffee | posthacking | jeff
564 (6 rows)
566 SELECT user_relns() AS user_relns
567 ORDER BY user_relns;
568 user_relns
569 ---------------------
570 a
571 a_star
572 abstime_tbl
573 aggtest
574 array_index_op_test
575 array_op_test
576 arrtest
577 b
578 b_star
579 box_tbl
580 bprime
581 bt_f8_heap
582 bt_i4_heap
583 bt_name_heap
584 bt_txt_heap
585 c
586 c_star
587 char_tbl
588 check2_tbl
589 check_seq
590 check_tbl
591 circle_tbl
592 city
593 copy_tbl
594 d
595 d_star
596 date_tbl
597 default_seq
598 default_tbl
599 defaultexpr_tbl
600 dept
601 e_star
602 emp
603 equipment_r
604 f_star
605 fast_emp4000
606 float4_tbl
607 float8_tbl
608 func_index_heap
609 hash_f8_heap
610 hash_i4_heap
611 hash_name_heap
612 hash_txt_heap
613 hobbies_r
614 iexit
615 ihighway
616 inet_tbl
617 inhe
618 inhf
619 inhx
620 insert_seq
621 insert_tbl
622 int2_tbl
623 int4_tbl
624 int8_tbl
625 interval_tbl
626 iportaltest
627 log_table
628 lseg_tbl
629 main_table
630 money_data
631 num_data
632 num_exp_add
633 num_exp_div
634 num_exp_ln
635 num_exp_log10
636 num_exp_mul
637 num_exp_power_10_ln
638 num_exp_sqrt
639 num_exp_sub
640 num_input_test
641 num_result
642 onek
643 onek2
644 path_tbl
645 person
646 point_tbl
647 polygon_tbl
648 ramp
649 random_tbl
650 real_city
651 reltime_tbl
652 road
653 shighway
654 slow_emp4000
655 street
656 stud_emp
657 student
658 subselect_tbl
659 tenk1
660 tenk2
661 test_tsvector
662 text_tbl
663 time_tbl
664 timestamp_tbl
665 timestamptz_tbl
666 timetz_tbl
667 tinterval_tbl
668 toyemp
669 varchar_tbl
670 xacttest
671 (101 rows)
673 SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
674 name
675 ------
676 guts
677 (1 row)
679 SELECT hobbies_by_name('basketball');
680 hobbies_by_name
681 -----------------
682 joe
683 (1 row)
685 SELECT name, overpaid(emp.*) FROM emp;
686 name | overpaid
687 --------+----------
688 sharon | t
689 sam | t
690 bill | t
691 jeff | f
692 cim | f
693 linda | f
694 (6 rows)
696 --
697 -- Try a few cases with SQL-spec row constructor expressions
698 --
699 SELECT * FROM equipment(ROW('skywalking', 'mer'));
700 name | hobby
701 ------+------------
702 guts | skywalking
703 (1 row)
705 SELECT name(equipment(ROW('skywalking', 'mer')));
706 name
707 ------
708 guts
709 (1 row)
711 SELECT *, name(equipment(h.*)) FROM hobbies_r h;
712 name | person | name
713 -------------+--------+---------------
714 posthacking | mike | advil
715 posthacking | mike | peet's coffee
716 posthacking | jeff | advil
717 posthacking | jeff | peet's coffee
718 basketball | joe | hightops
719 basketball | sally | hightops
720 skywalking | | guts
721 (7 rows)
723 SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
724 name | person | name
725 -------------+--------+---------------
726 posthacking | mike | advil
727 posthacking | mike | peet's coffee
728 posthacking | jeff | advil
729 posthacking | jeff | peet's coffee
730 basketball | joe | hightops
731 basketball | sally | hightops
732 skywalking | | guts
733 (7 rows)
735 --
736 -- check that old-style C functions work properly with TOASTed values
737 --
738 create table oldstyle_test(i int4, t text);
739 insert into oldstyle_test values(null,null);
740 insert into oldstyle_test values(0,'12');
741 insert into oldstyle_test values(1000,'12');
742 insert into oldstyle_test values(0, repeat('x', 50000));
743 select i, length(t), octet_length(t), oldstyle_length(i,t) from oldstyle_test;
744 i | length | octet_length | oldstyle_length
745 ------+--------+--------------+-----------------
746 | | |
747 0 | 2 | 2 | 2
748 1000 | 2 | 2 | 1002
749 0 | 50000 | 50000 | 50000
750 (4 rows)
752 drop table oldstyle_test;
753 --
754 -- functional joins
755 --
756 --
757 -- instance rules
758 --
759 --
760 -- rewrite rules
761 --