107.943

(1 row)

SELECT avg(gpa) AS avg_3_4 FROM student;

SELECT avg(gpa) AS avg_3_4 FROMONLYstudent;

avg_3_4

---------

3.4

431.773

(1 row)

SELECT sum(gpa) AS avg_6_8 FROM student;

SELECT sum(gpa) AS avg_6_8 FROMONLYstudent;

avg_6_8

---------

6.8

--

CREATE VIEW street AS

SELECT r.name, r.thepath, c.cname AS cname

FROM road r, real_city c

FROMONLYroad r, real_city c

WHERE c.outline ## r.thepath;

CREATE VIEW iexit AS

SELECT ih.name, ih.thepath,

pg_indexes | SELECT c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(x.indexrelid) AS indexdef FROM pg_index x, pg_class c, pg_class i WHERE ((c.oid = x.indrelid) AND (i.oid = x.indexrelid));

pg_rules | SELECT c.relname AS tablename, r.rulename, pg_get_ruledef(r.rulename) AS definition FROM pg_rewrite r, pg_class c WHERE ((r.rulename !~ '^_RET'::text) AND (c.oid = r.ev_class));

pg_tables | SELECT c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, (c.reltriggers > 0) AS hastriggers FROM pg_class c WHERE (((c.relkind = 'r'::"char") OR (c.relkind = 's'::"char")) AND (NOT (EXISTS (SELECT pg_rewrite.rulename FROM pg_rewrite WHERE ((pg_rewrite.ev_class = c.oid) AND (pg_rewrite.ev_type = '1'::"char"))))));

pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usetrace, pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil FROM pg_shadow;

pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usecreatetable, pg_shadow.uselocktable, pg_shadow.usetrace, pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil FROM pg_shadow;

pg_views | SELECT c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.relname) AS definition FROM pg_class c WHERE (c.relhasrules AND (EXISTS (SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class = c.oid) AND (r.ev_type = '1'::"char")))));

rtest_v1 | SELECT rtest_t1.a, rtest_t1.b FROM rtest_t1;

rtest_vcomp | SELECT x.part, (x.size * y.factor) AS size_in_cm FROM rtest_comp x, rtest_unitfact y WHERE (x.unit = y.unit);

shoelace | SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, (s.sl_len * u.un_fact) AS sl_len_cm FROM shoelace_data s, unit u WHERE (s.sl_unit = u.un_name);

shoelace_candelete | SELECT shoelace_obsolete.sl_name, shoelace_obsolete.sl_avail, shoelace_obsolete.sl_color, shoelace_obsolete.sl_len, shoelace_obsolete.sl_unit, shoelace_obsolete.sl_len_cm FROM shoelace_obsolete WHERE (shoelace_obsolete.sl_avail = 0);

shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));

street | SELECT r.name, r.thepath, c.cname FROM road r, real_city c WHERE (c.outline ## r.thepath);

street | SELECT r.name, r.thepath, c.cname FROMONLYroad r, real_city c WHERE (c.outline ## r.thepath);

toyemp | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;

(20 rows)

FROM a_star* x

WHERE aa ISNULL;

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

FROM a_star* x

WHERE x.foo >= 2;

ALTER TABLE a_star RENAME COLUMN foo TO aa;

-- joe and sally play basketball, and

-- everyone else does nothing.

--

SELECT p.name, p.hobbies.name FROM person p;

SELECT p.name, p.hobbies.name FROMONLYperson p;

--

-- as above, but jeff also does post_hacking.

-- joe and sally need hightops, and

-- everyone else is fine.

--

SELECT p.name, p.hobbies.name, p.hobbies.equipment.name FROM person p;

SELECT p.name, p.hobbies.name, p.hobbies.equipment.name FROMONLYperson p;

--

-- as above, but jeff needs advil and peet's coffee as well.

-- just like the last two, but make sure that the target list fixup and

-- unflattening is being done correctly.

--

SELECT p.hobbies.equipment.name, p.name, p.hobbies.name FROM person p;

SELECT p.hobbies.equipment.name, p.name, p.hobbies.name FROMONLYperson p;

SELECT p.hobbies.equipment.name, p.name, p.hobbies.name FROM person* p;

SELECT p.hobbies.equipment.name, p.hobbies.name, p.name FROM person p;

SELECT p.hobbies.equipment.name, p.hobbies.name, p.name FROMONLYperson p;

SELECT p.hobbies.equipment.name, p.hobbies.name, p.name FROM person* p;

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

FROM a_star* x

WHERE x.foo >= 2;

class | foo

-------+-----

a | 2

(1 row)

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 *

-- joe and sally play basketball, and

-- everyone else does nothing.

--

SELECT p.name, p.hobbies.name FROM person p;

SELECT p.name, p.hobbies.name FROMONLYperson p;

name | name

-------+-------------

mike | posthacking

-- joe and sally need hightops, and

-- everyone else is fine.

--

SELECT p.name, p.hobbies.name, p.hobbies.equipment.name FROM person p;

SELECT p.name, p.hobbies.name, p.hobbies.equipment.name FROMONLYperson p;

name | name | name

-------+-------------+---------------

mike | posthacking | advil

-- just like the last two, but make sure that the target list fixup and

-- unflattening is being done correctly.

--

SELECT p.hobbies.equipment.name, p.name, p.hobbies.name FROM person p;

SELECT p.hobbies.equipment.name, p.name, p.hobbies.name FROMONLYperson p;

name | name | name

---------------+-------+-------------

advil | mike | posthacking

peet's coffee | jeff | posthacking

(6 rows)

SELECT p.hobbies.equipment.name, p.hobbies.name, p.name FROM person p;

SELECT p.hobbies.equipment.name, p.hobbies.name, p.name FROMONLYperson p;

name | name | name

---------------+-------------+-------

advil | posthacking | mike

ORDER BY user_relns;

user_relns

---------------------

a

a_star

abstime_tbl

aggtest

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

circle_tbl

city

copy_tbl

d

d_star

default_seq

default_tbl

toyemp

varchar_tbl

xacttest

(86 rows)

(90 rows)

--SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name;

--

SELECTavg(b)AS avg_107_943FROM aggtest;

SELECTavg(gpa)AS avg_3_4FROM student;

SELECTavg(gpa)AS avg_3_4FROMONLYstudent;

SELECTsum(four)AS sum_1500FROM onek;

SELECTsum(b)AS avg_431_773FROM aggtest;

SELECTsum(gpa)AS avg_6_8FROM student;

SELECTsum(gpa)AS avg_6_8FROMONLYstudent;

SELECTmax(four)AS max_3FROM onek;

SELECTr.name,r.thepath,c.cnameAS cname

FROM road r, real_city c

FROMONLYroad r, real_city c

WHEREc.outline## r.thepath;