summaryrefslogtreecommitdiffstats
path: root/src/test/regress/output/misc.source
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/output/misc.source')
-rw-r--r--src/test/regress/output/misc.source692
1 files changed, 692 insertions, 0 deletions
diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source
new file mode 100644
index 0000000..b9595cc
--- /dev/null
+++ b/src/test/regress/output/misc.source
@@ -0,0 +1,692 @@
+--
+-- 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"
+-- That ALTER TABLE should have added TOAST tables.
+SELECT relname, reltoastrelid <> 0 AS has_toast_table
+ FROM pg_class
+ WHERE oid::regclass IN ('a_star', 'c_star')
+ ORDER BY 1;
+ relname | has_toast_table
+---------+-----------------
+ a_star | t
+ c_star | t
+(2 rows)
+
+--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
+ ORDER BY 1,2;
+ 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 name(equipment(hobby_construct(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment(hobby_construct_named(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named(hobby_construct_named(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_1a(hobby_construct_named(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_1b(hobby_construct_named(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_1c(hobby_construct_named(text 'skywalking', text 'mer')));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_2a(text 'skywalking'));
+ name
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_2b(text 'skywalking'));
+ name
+---------------
+ advil
+ peet's coffee
+ hightops
+ guts
+(4 rows)
+
+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)
+
+--
+-- functional joins
+--
+--
+-- instance rules
+--
+--
+-- rewrite rules
+--
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-02 02:27:52 +0000