Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 398 insertions, 0 deletions

diff --git a/src/test/regress/expected/misc.out b/src/test/regress/expected/misc.out
new file mode 100644
index 0000000..6e816c5
--- /dev/null
+++ b/src/test/regress/expected/misc.out
@@ -0,0 +1,398 @@
+--
+-- MISC
+--
+-- directory paths and dlsuffix are passed to us in environment variables
+\getenv abs_srcdir PG_ABS_SRCDIR
+\getenv abs_builddir PG_ABS_BUILDDIR
+\getenv libdir PG_LIBDIR
+\getenv dlsuffix PG_DLSUFFIX
+\set regresslib :libdir '/regress' :dlsuffix
+CREATE FUNCTION overpaid(emp)
+   RETURNS bool
+   AS :'regresslib'
+   LANGUAGE C STRICT;
+CREATE FUNCTION reverse_name(name)
+   RETURNS name
+   AS :'regresslib'
+   LANGUAGE C STRICT;
+--
+-- 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)
+--
+SELECT two, stringu1, ten, string4
+   INTO TABLE tmp
+   FROM onek;
+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
+--
+\set filename :abs_builddir '/results/onek.data'
+COPY onek TO :'filename';
+CREATE TEMP TABLE onek_copy (LIKE onek);
+COPY onek_copy FROM :'filename';
+SELECT * FROM onek EXCEPT ALL SELECT * FROM onek_copy;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+(0 rows)
+
+SELECT * FROM onek_copy EXCEPT ALL SELECT * FROM onek;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+(0 rows)
+
+\set filename :abs_builddir '/results/stud_emp.data'
+COPY BINARY stud_emp TO :'filename';
+CREATE TEMP TABLE stud_emp_copy (LIKE stud_emp);
+COPY BINARY stud_emp_copy FROM :'filename';
+SELECT * FROM stud_emp_copy;
+ 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)
+
+--
+-- test data for postquel functions
+--
+CREATE TABLE hobbies_r (
+	name		text,
+	person 		text
+);
+CREATE TABLE equipment_r (
+	name 		text,
+	hobby		text
+);
+INSERT INTO hobbies_r (name, person)
+   SELECT 'posthacking', p.name
+   FROM person* p
+   WHERE p.name = 'mike' or p.name = 'jeff';
+INSERT INTO hobbies_r (name, person)
+   SELECT 'basketball', p.name
+   FROM person p
+   WHERE p.name = 'joe' or p.name = 'sally';
+INSERT INTO hobbies_r (name) VALUES ('skywalking');
+INSERT INTO equipment_r (name, hobby) VALUES ('advil', 'posthacking');
+INSERT INTO equipment_r (name, hobby) VALUES ('peet''s coffee', 'posthacking');
+INSERT INTO equipment_r (name, hobby) VALUES ('hightops', 'basketball');
+INSERT INTO equipment_r (name, hobby) VALUES ('guts', 'skywalking');
+--
+-- postquel functions
+--
+CREATE FUNCTION hobbies(person)
+   RETURNS setof hobbies_r
+   AS 'select * from hobbies_r where person = $1.name'
+   LANGUAGE SQL;
+CREATE FUNCTION hobby_construct(text, text)
+   RETURNS hobbies_r
+   AS 'select $1 as name, $2 as hobby'
+   LANGUAGE SQL;
+CREATE FUNCTION hobby_construct_named(name text, hobby text)
+   RETURNS hobbies_r
+   AS 'select name, hobby'
+   LANGUAGE SQL;
+CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE)
+   RETURNS hobbies_r.person%TYPE
+   AS 'select person from hobbies_r where name = $1'
+   LANGUAGE SQL;
+NOTICE:  type reference hobbies_r.name%TYPE converted to text
+NOTICE:  type reference hobbies_r.person%TYPE converted to text
+CREATE FUNCTION equipment(hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = $1.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = equipment_named.hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_1a(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_1a.hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_1b(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_1c(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_2a(hobby text)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_2a.hobby'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_2b(hobby text)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = hobby'
+   LANGUAGE SQL;
+--
+-- 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
+--