---- MISC------ BTREE--UPDATE onek   SET unique1 = onek.unique1 + 1;UPDATE onek   SET unique1 = onek.unique1 - 1;---- BTREE partial---- UPDATE onek2--   SET unique1 = onek2.unique1 + 1;--UPDATE onek2 --   SET unique1 = onek2.unique1 - 1;---- BTREE shutting out non-functional updates---- the following two tests seem to take a long time on some -- systems.    This non-func update stuff needs to be examined-- more closely.  			- jolly (2/22/96)-- UPDATE tmp   SET stringu1 = reverse_name(onek.stringu1)   FROM onek   WHERE onek.stringu1 = 'JBAAAA' and	  onek.stringu1 = tmp.stringu1;UPDATE tmp   SET stringu1 = reverse_name(onek2.stringu1)   FROM onek2   WHERE onek2.stringu1 = 'JCAAAA' and	  onek2.stringu1 = tmp.stringu1;DROP TABLE tmp;--UPDATE person*--   SET age = age + 1;--UPDATE person*--   SET age = age + 3--   WHERE name = 'linda';---- copy--COPY onek TO '@abs_builddir@/results/onek.data';DELETE FROM onek;COPY onek FROM '@abs_builddir@/results/onek.data';SELECT unique1 FROM onek WHERE unique1 < 2 ORDER BY unique1; unique1 ---------       0       1(2 rows)DELETE FROM onek2;COPY onek2 FROM '@abs_builddir@/results/onek.data';SELECT unique1 FROM onek2 WHERE unique1 < 2 ORDER BY unique1; unique1 ---------       0       1(2 rows)COPY BINARY stud_emp TO '@abs_builddir@/results/stud_emp.data';DELETE FROM stud_emp;COPY BINARY stud_emp FROM '@abs_builddir@/results/stud_emp.data';SELECT * FROM stud_emp; name  | age |  location  | salary | manager | gpa | percent -------+-----+------------+--------+---------+-----+--------- jeff  |  23 | (8,7.7)    |    600 | sharon  | 3.5 |         cim   |  30 | (10.5,4.7) |    400 |         | 3.4 |         linda |  19 | (0.9,6.1)  |    100 |         | 2.9 |        (3 rows)-- COPY aggtest FROM stdin;-- 56	7.8-- 100	99.097-- 0	0.09561-- 42	324.78-- .-- COPY aggtest TO stdout;---- inheritance stress test--SELECT * FROM a_star*; class | a  -------+---- a     |  1 a     |  2 a     |    b     |  3 b     |  4 b     |    b     |    c     |  5 c     |  6 c     |    c     |    d     |  7 d     |  8 d     |  9 d     | 10 d     |    d     | 11 d     | 12 d     | 13 d     |    d     |    d     |    d     | 14 d     |    d     |    d     |    d     |    e     | 15 e     | 16 e     | 17 e     |    e     | 18 e     |    e     |    f     | 19 f     | 20 f     | 21 f     | 22 f     |    f     | 24 f     | 25 f     | 26 f     |    f     |    f     |    f     | 27 f     |    f     |    f     |    f     |   (50 rows)SELECT *    FROM b_star* x   WHERE x.b = text 'bumble' or x.a < 3; class | a |   b    -------+---+-------- b     |   | bumble(1 row)SELECT class, a    FROM c_star* x    WHERE x.c ~ text 'hi'; class | a  -------+---- c     |  5 c     |    d     |  7 d     |  8 d     | 10 d     |    d     | 12 d     |    d     |    d     |    e     | 15 e     | 16 e     |    e     |    f     | 19 f     | 20 f     | 21 f     |    f     | 24 f     |    f     |    f     |   (22 rows)SELECT class, b, c   FROM d_star* x   WHERE x.a < 100; class |    b    |     c      -------+---------+------------ d     | grumble | hi sunita d     | stumble | hi koko d     | rumble  |  d     |         | hi kristin d     | fumble  |  d     |         | hi avi d     |         |  d     |         | (8 rows)SELECT class, c FROM e_star* x WHERE x.c NOTNULL; class |      c      -------+------------- e     | hi carol e     | hi bob e     | hi michelle e     | hi elisa f     | hi claire f     | hi mike f     | hi marcel f     | hi keith f     | hi marc f     | hi allison f     | hi jeff f     | hi carl(12 rows)SELECT * FROM f_star* x WHERE x.c ISNULL; class | a  | c |  e  |                     f                     -------+----+---+-----+------------------------------------------- f     | 22 |   |  -7 | ((111,555),(222,666),(333,777),(444,888)) f     | 25 |   |  -9 |  f     | 26 |   |     | ((11111,33333),(22222,44444)) f     |    |   | -11 | ((1111111,3333333),(2222222,4444444)) f     | 27 |   |     |  f     |    |   | -12 |  f     |    |   |     | ((11111111,33333333),(22222222,44444444)) f     |    |   |     | (8 rows)-- grouping and aggregation on inherited sets have been busted in the past...SELECT sum(a) FROM a_star*; sum ----- 355(1 row)SELECT class, sum(a) FROM a_star* GROUP BY class ORDER BY class; class | sum -------+----- a     |   3 b     |   7 c     |  11 d     |  84 e     |  66 f     | 184(6 rows)ALTER TABLE f_star RENAME COLUMN f TO ff;ALTER TABLE e_star* RENAME COLUMN e TO ee;ALTER TABLE d_star* RENAME COLUMN d TO dd;ALTER TABLE c_star* RENAME COLUMN c TO cc;ALTER TABLE b_star* RENAME COLUMN b TO bb;ALTER TABLE a_star* RENAME COLUMN a TO aa;SELECT class, aa   FROM a_star* x   WHERE aa ISNULL; class | aa -------+---- a     |    b     |    b     |    c     |    c     |    d     |    d     |    d     |    d     |    d     |    d     |    d     |    d     |    e     |    e     |    e     |    f     |    f     |    f     |    f     |    f     |    f     |    f     |    f     |   (24 rows)-- As of Postgres 7.1, ALTER implicitly recurses,-- so this should be same as ALTER a_star*ALTER TABLE a_star RENAME COLUMN aa TO foo;SELECT class, foo   FROM a_star* x   WHERE x.foo >= 2; class | foo -------+----- a     |   2 b     |   3 b     |   4 c     |   5 c     |   6 d     |   7 d     |   8 d     |   9 d     |  10 d     |  11 d     |  12 d     |  13 d     |  14 e     |  15 e     |  16 e     |  17 e     |  18 f     |  19 f     |  20 f     |  21 f     |  22 f     |  24 f     |  25 f     |  26 f     |  27(25 rows)ALTER TABLE a_star RENAME COLUMN foo TO aa;SELECT *    from a_star*   WHERE aa < 1000; class | aa -------+---- a     |  1 a     |  2 b     |  3 b     |  4 c     |  5 c     |  6 d     |  7 d     |  8 d     |  9 d     | 10 d     | 11 d     | 12 d     | 13 d     | 14 e     | 15 e     | 16 e     | 17 e     | 18 f     | 19 f     | 20 f     | 21 f     | 22 f     | 24 f     | 25 f     | 26 f     | 27(26 rows)ALTER TABLE f_star ADD COLUMN f int4;UPDATE f_star SET f = 10;ALTER TABLE e_star* ADD COLUMN e int4;--UPDATE e_star* SET e = 42;SELECT * FROM e_star*; class | aa |     cc      | ee  | e -------+----+-------------+-----+--- e     | 15 | hi carol    |  -1 |   e     | 16 | hi bob      |     |   e     | 17 |             |  -2 |   e     |    | hi michelle |  -3 |   e     | 18 |             |     |   e     |    | hi elisa    |     |   e     |    |             |  -4 |   f     | 19 | hi claire   |  -5 |   f     | 20 | hi mike     |  -6 |   f     | 21 | hi marcel   |     |   f     | 22 |             |  -7 |   f     |    | hi keith    |  -8 |   f     | 24 | hi marc     |     |   f     | 25 |             |  -9 |   f     | 26 |             |     |   f     |    | hi allison  | -10 |   f     |    | hi jeff     |     |   f     |    |             | -11 |   f     | 27 |             |     |   f     |    | hi carl     |     |   f     |    |             | -12 |   f     |    |             |     |   f     |    |             |     |  (23 rows)ALTER TABLE a_star* ADD COLUMN a text;NOTICE:  merging definition of column "a" for child "d_star"--UPDATE b_star*--   SET a = text 'gazpacho'--   WHERE aa > 4;SELECT class, aa, a FROM a_star*; class | aa | a -------+----+--- a     |  1 |  a     |  2 |  a     |    |  b     |  3 |  b     |  4 |  b     |    |  b     |    |  c     |  5 |  c     |  6 |  c     |    |  c     |    |  d     |  7 |  d     |  8 |  d     |  9 |  d     | 10 |  d     |    |  d     | 11 |  d     | 12 |  d     | 13 |  d     |    |  d     |    |  d     |    |  d     | 14 |  d     |    |  d     |    |  d     |    |  d     |    |  e     | 15 |  e     | 16 |  e     | 17 |  e     |    |  e     | 18 |  e     |    |  e     |    |  f     | 19 |  f     | 20 |  f     | 21 |  f     | 22 |  f     |    |  f     | 24 |  f     | 25 |  f     | 26 |  f     |    |  f     |    |  f     |    |  f     | 27 |  f     |    |  f     |    |  f     |    |  f     |    | (50 rows)---- versions------ postquel functions------ mike does post_hacking,-- joe and sally play basketball, and-- everyone else does nothing.--SELECT p.name, name(p.hobbies) FROM ONLY person p; name  |    name     -------+------------- mike  | posthacking joe   | basketball sally | basketball(3 rows)---- as above, but jeff also does post_hacking.--SELECT p.name, name(p.hobbies) FROM person* p; name  |    name     -------+------------- mike  | posthacking joe   | basketball sally | basketball jeff  | posthacking(4 rows)---- the next two queries demonstrate how functions generate bogus duplicates.-- this is a "feature" ..--SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r;    name     |     name      -------------+--------------- basketball  | hightops posthacking | advil posthacking | peet's coffee skywalking  | guts(4 rows)SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r;    name     |     name      -------------+--------------- posthacking | advil posthacking | peet's coffee posthacking | advil posthacking | peet's coffee basketball  | hightops basketball  | hightops skywalking  | guts(7 rows)---- mike needs advil and peet's coffee,-- joe and sally need hightops, and-- everyone else is fine.--SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p; name  |    name     |     name      -------+-------------+--------------- mike  | posthacking | advil mike  | posthacking | peet's coffee joe   | basketball  | hightops sally | basketball  | hightops(4 rows)---- as above, but jeff needs advil and peet's coffee as well.--SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p; name  |    name     |     name      -------+-------------+--------------- mike  | posthacking | advil mike  | posthacking | peet's coffee joe   | basketball  | hightops sally | basketball  | hightops jeff  | posthacking | advil jeff  | posthacking | peet's coffee(6 rows)---- just like the last two, but make sure that the target list fixup and-- unflattening is being done correctly.--SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p;     name      | name  |    name     ---------------+-------+------------- advil         | mike  | posthacking peet's coffee | mike  | posthacking hightops      | joe   | basketball hightops      | sally | basketball(4 rows)SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p;     name      | name  |    name     ---------------+-------+------------- advil         | mike  | posthacking peet's coffee | mike  | posthacking hightops      | joe   | basketball hightops      | sally | basketball advil         | jeff  | posthacking peet's coffee | jeff  | posthacking(6 rows)SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p;     name      |    name     | name  ---------------+-------------+------- advil         | posthacking | mike peet's coffee | posthacking | mike hightops      | basketball  | joe hightops      | basketball  | sally(4 rows)SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p;     name      |    name     | name  ---------------+-------------+------- advil         | posthacking | mike peet's coffee | posthacking | mike hightops      | basketball  | joe hightops      | basketball  | sally advil         | posthacking | jeff peet's coffee | posthacking | jeff(6 rows)SELECT user_relns() AS user_relns   ORDER BY user_relns;     user_relns      --------------------- a a_star abstime_tbl aggtest array_index_op_test array_op_test arrtest b b_star box_tbl bprime bt_f8_heap bt_i4_heap bt_name_heap bt_txt_heap c c_star char_tbl check2_tbl check_seq check_tbl circle_tbl city copy_tbl d d_star date_tbl default_seq default_tbl defaultexpr_tbl dept e_star emp equipment_r f_star fast_emp4000 float4_tbl float8_tbl func_index_heap hash_f8_heap hash_i4_heap hash_name_heap hash_txt_heap hobbies_r iexit ihighway inet_tbl inhe inhf inhx insert_seq insert_tbl int2_tbl int4_tbl int8_tbl interval_tbl iportaltest log_table lseg_tbl main_table money_data num_data num_exp_add num_exp_div num_exp_ln num_exp_log10 num_exp_mul num_exp_power_10_ln num_exp_sqrt num_exp_sub num_input_test num_result onek onek2 path_tbl person point_tbl polygon_tbl ramp random_tbl real_city reltime_tbl road shighway slow_emp4000 street stud_emp student subselect_tbl tenk1 tenk2 test_tsvector text_tbl time_tbl timestamp_tbl timestamptz_tbl timetz_tbl tinterval_tbl toyemp varchar_tbl xacttest(101 rows)SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))); name ------ guts(1 row)SELECT hobbies_by_name('basketball'); hobbies_by_name ----------------- joe(1 row)SELECT name, overpaid(emp.*) FROM emp;  name  | overpaid --------+---------- sharon | t sam    | t bill   | t jeff   | f cim    | f linda  | f(6 rows)---- Try a few cases with SQL-spec row constructor expressions--SELECT * FROM equipment(ROW('skywalking', 'mer')); name |   hobby    ------+------------ guts | skywalking(1 row)SELECT name(equipment(ROW('skywalking', 'mer'))); name ------ guts(1 row)SELECT *, name(equipment(h.*)) FROM hobbies_r h;    name     | person |     name      -------------+--------+--------------- posthacking | mike   | advil posthacking | mike   | peet's coffee posthacking | jeff   | advil posthacking | jeff   | peet's coffee basketball  | joe    | hightops basketball  | sally  | hightops skywalking  |        | guts(7 rows)SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;    name     | person |     name      -------------+--------+--------------- posthacking | mike   | advil posthacking | mike   | peet's coffee posthacking | jeff   | advil posthacking | jeff   | peet's coffee basketball  | joe    | hightops basketball  | sally  | hightops skywalking  |        | guts(7 rows)---- check that old-style C functions work properly with TOASTed values--create table oldstyle_test(i int4, t text);insert into oldstyle_test values(null,null);insert into oldstyle_test values(0,'12');insert into oldstyle_test values(1000,'12');insert into oldstyle_test values(0, repeat('x', 50000));select i, length(t), octet_length(t), oldstyle_length(i,t) from oldstyle_test;  i   | length | octet_length | oldstyle_length ------+--------+--------------+-----------------      |        |              |                    0 |      2 |            2 |               2 1000 |      2 |            2 |            1002    0 |  50000 |        50000 |           50000(4 rows)drop table oldstyle_test;---- functional joins------ instance rules------ rewrite rules--