--
-- 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)
--
-- 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
--