diff options
Diffstat (limited to 'src/test/regress/expected/misc.out')
| -rw-r--r-- | src/test/regress/expected/misc.out | 398 |
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 +-- |