8 SET unique1 = onek.unique1 + 1;
10 SET unique1 = onek.unique1 - 1;
15 -- SET unique1 = onek2.unique1 + 1;
17 -- SET unique1 = onek2.unique1 - 1;
19 -- BTREE shutting out non-functional updates
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)
26 SET stringu1 = reverse_name(onek.stringu1)
27 WHERE onek.stringu1 = 'JBAAAA' and
28 onek.stringu1 = tmp.stringu1;
30 SET stringu1 = reverse_name(onek2.stringu1)
31 WHERE onek2.stringu1 = 'JCAAAA' and
32 onek2.stringu1 = tmp.stringu1;
38 -- WHERE name = 'linda';
42 COPY onek TO '@abs_builddir@/results/onek.data';
44 COPY onek FROM '@abs_builddir@/results/onek.data';
45 SELECT unique1 FROM onek WHERE unique1 < 2 ORDER BY unique1;
53 COPY onek2 FROM '@abs_builddir@/results/onek.data';
54 SELECT unique1 FROM onek2 WHERE unique1 < 2 ORDER BY unique1;
61 COPY BINARY stud_emp TO '@abs_builddir@/results/stud_emp.data';
63 COPY BINARY stud_emp FROM '@abs_builddir@/results/stud_emp.data';
64 SELECT * FROM stud_emp;
65 name | age | location | salary | manager | gpa | percent
66 -------+-----+------------+--------+---------+-----+---------
67 jeff | 23 | (8,7.7) | 600 | sharon | 3.5 |
68 cim | 30 | (10.5,4.7) | 400 | | 3.4 |
69 linda | 19 | (0.9,6.1) | 100 | | 2.9 |
72 -- COPY aggtest FROM stdin;
78 -- COPY aggtest TO stdout;
80 -- inheritance stress test
82 SELECT * FROM a_star*;
139 WHERE x.b = text 'bumble' or x.a < 3;
147 WHERE x.c ~ text 'hi';
178 -------+---------+------------
179 d | grumble | hi sunita
180 d | stumble | hi koko
189 SELECT class, c FROM e_star* x WHERE x.c NOTNULL;
191 -------+-------------
206 SELECT * FROM f_star* x WHERE x.c ISNULL;
207 class | a | c | e | f
208 -------+----+---+-----+-------------------------------------------
209 f | 22 | | -7 | ((111,555),(222,666),(333,777),(444,888))
211 f | 26 | | | ((11111,33333),(22222,44444))
212 f | | | -11 | ((1111111,3333333),(2222222,4444444))
215 f | | | | ((11111111,33333333),(22222222,44444444))
219 -- grouping and aggregation on inherited sets have been busted in the past...
220 SELECT sum(a) FROM a_star*;
226 SELECT class, sum(a) FROM a_star* GROUP BY class ORDER BY class;
237 ALTER TABLE f_star RENAME COLUMN f TO ff;
238 ALTER TABLE e_star* RENAME COLUMN e TO ee;
239 ALTER TABLE d_star* RENAME COLUMN d TO dd;
240 ALTER TABLE c_star* RENAME COLUMN c TO cc;
241 ALTER TABLE b_star* RENAME COLUMN b TO bb;
242 ALTER TABLE a_star* RENAME COLUMN a TO aa;
274 -- As of Postgres 7.1, ALTER implicitly recurses,
275 -- so this should be same as ALTER a_star*
276 ALTER TABLE a_star RENAME COLUMN aa TO foo;
309 ALTER TABLE a_star RENAME COLUMN foo TO aa;
343 ALTER TABLE f_star ADD COLUMN f int4;
344 UPDATE f_star SET f = 10;
345 ALTER TABLE e_star* ADD COLUMN e int4;
346 --UPDATE e_star* SET e = 42;
347 SELECT * FROM e_star*;
348 class | aa | cc | ee | e
349 -------+----+-------------+-----+---
350 e | 15 | hi carol | -1 |
353 e | | hi michelle | -3 |
357 f | 19 | hi claire | -5 |
358 f | 20 | hi mike | -6 |
359 f | 21 | hi marcel | |
361 f | | hi keith | -8 |
365 f | | hi allison | -10 |
375 ALTER TABLE a_star* ADD COLUMN a text;
376 NOTICE: ALTER TABLE: merging definition of column "a" for child d_star
378 -- SET a = text 'gazpacho'
380 SELECT class, aa, a FROM a_star*;
439 -- postquel functions
442 -- mike does post_hacking,
443 -- joe and sally play basketball, and
444 -- everyone else does nothing.
446 SELECT p.name, name(p.hobbies) FROM ONLY person p;
448 -------+-------------
455 -- as above, but jeff also does post_hacking.
457 SELECT p.name, name(p.hobbies) FROM person* p;
459 -------+-------------
467 -- the next two queries demonstrate how functions generate bogus duplicates.
468 -- this is a "feature" ..
470 SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r;
472 -------------+---------------
473 basketball | hightops
475 posthacking | peet's coffee
479 SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r;
481 -------------+---------------
483 posthacking | peet's coffee
485 posthacking | peet's coffee
486 basketball | hightops
487 basketball | hightops
492 -- mike needs advil and peet's coffee,
493 -- joe and sally need hightops, and
494 -- everyone else is fine.
496 SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p;
498 -------+-------------+---------------
499 mike | posthacking | advil
500 mike | posthacking | peet's coffee
501 joe | basketball | hightops
502 sally | basketball | hightops
506 -- as above, but jeff needs advil and peet's coffee as well.
508 SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p;
510 -------+-------------+---------------
511 mike | posthacking | advil
512 mike | posthacking | peet's coffee
513 joe | basketball | hightops
514 sally | basketball | hightops
515 jeff | posthacking | advil
516 jeff | posthacking | peet's coffee
520 -- just like the last two, but make sure that the target list fixup and
521 -- unflattening is being done correctly.
523 SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p;
525 ---------------+-------+-------------
526 advil | mike | posthacking
527 peet's coffee | mike | posthacking
528 hightops | joe | basketball
529 hightops | sally | basketball
532 SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p;
534 ---------------+-------+-------------
535 advil | mike | posthacking
536 peet's coffee | mike | posthacking
537 hightops | joe | basketball
538 hightops | sally | basketball
539 advil | jeff | posthacking
540 peet's coffee | jeff | posthacking
543 SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p;
545 ---------------+-------------+-------
546 advil | posthacking | mike
547 peet's coffee | posthacking | mike
548 hightops | basketball | joe
549 hightops | basketball | sally
552 SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p;
554 ---------------+-------------+-------
555 advil | posthacking | mike
556 peet's coffee | posthacking | mike
557 hightops | basketball | joe
558 hightops | basketball | sally
559 advil | posthacking | jeff
560 peet's coffee | posthacking | jeff
563 SELECT user_relns() AS user_relns
566 ---------------------
662 --SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name;
663 SELECT hobbies_by_name('basketball');
670 -- check that old-style C functions work properly with TOASTed values
672 create table oldstyle_test(i int4, t text);
673 insert into oldstyle_test values(null,null);
674 insert into oldstyle_test values(0,'12');
675 insert into oldstyle_test values(1000,'12');
676 insert into oldstyle_test values(0, repeat('x', 50000));
677 select i, length(t), octet_length(t), oldstyle_length(i,t) from oldstyle_test;
678 i | length | octet_length | oldstyle_length
679 ------+--------+--------------+-----------------
683 0 | 50000 | 50000 | 50000
686 drop table oldstyle_test;