/* * create.source * * $Header: /usr/local/devel/postgres/src/regress/regress/RCS/create.source,v 1.12 1994/02/15 09:59:53 aoki Exp $ */ /* * ABSTRACT DATA TYPE DEFINITIONS */ define function circle_in ( language = "c", returntype = circle ) arg is (any) as "_OBJWD_/regress_SLSUFF_" \g define function circle_out ( language = "c", returntype = any ) arg is (any) as "_OBJWD_/regress_SLSUFF_" \g define type circle ( internallength = 24, input = circle_in, output = circle_out ) \g define type city_budget ( internallength = 16, input = int44in, output = int44out, element = int4 ) \g /* * CLASS DEFINITIONS */ create hobbies_r (name = text, person = text) \g create equipment_r (name = text, hobby = text) \g create onek(unique1=int4, unique2=int4, two=int4, four=int4, ten=int4, twenty=int4, hundred=int4, thousand=int4, twothousand=int4, fivethous=int4, tenthous=int4, odd=int4, even=int4, stringu1=char16, stringu2=char16, string4=char16) \g create tenk1 (unique1=int4, unique2=int4, two=int4, four=int4, ten=int4, twenty=int4, hundred=int4, thousand=int4, twothousand=int4, fivethous=int4, tenthous=int4, odd=int4, even=int4, stringu1=char16, stringu2=char16, string4=char16) \g create tenk2 (unique1 = int4, unique2 = int4, two = int4, four = int4, ten=int4, twenty = int4, hundred = int4, thousand = int4, twothousand = int4, fivethous = int4, tenthous=int4, odd=int4, even=int4, stringu1=char16, stringu2=char16, string4=char16) \g create person (name = text, age = int4, location = point) \g create emp (salary = int4, manager = char16) inherits (person) \g create student (gpa = float8) inherits (person) \g create stud_emp (percent = int4) inherits (emp, student) \g create city (name = char16, location = box, budget = city_budget) \g create dept (dname = char16, mgrname = text) \g create slow_emp4000 (home_base = box) \g create fast_emp4000 (home_base = box) \g create road (name = text, thepath = path) \g create ihighway () inherits (road) \g create shighway (surface = text) inherits (road) \g create real_city (pop = int4, cname = text, outline = path) \g /* * test the "star" operators a bit more thoroughly -- this time, * throw in lots of NULL fields... * * a is the type root * b and c inherit from a (one-level single inheritance) * d inherits from b and c (two-level multiple inheritance) * e inherits from c (two-level single inheritance) * f inherits from e (three-level single inheritance) */ create a_star (class = char, a = int4) \g create b_star (b = text) inherits (a_star) \g create c_star (c = char16) inherits (a_star) \g create d_star (d = float8) inherits (b_star, c_star) \g create e_star (e = int2) inherits (c_star) \g create f_star (f = polygon) inherits (e_star) \g create aggtest (a = int2, b = float4) \g create arrtest (a = int2[], b = int4[][][], c = char16[], d = text[][], e = float8[]) \g create hash_i4_heap (seqno = int4, random = int4) \g create hash_c16_heap (seqno = int4, random = char16) \g create hash_txt_heap (seqno = int4, random = text) \g create hash_f8_heap (seqno = int4, random = float8) \g create bt_i4_heap (seqno = int4, random = int4) \g create bt_c16_heap (seqno = char16, random = int4) \g create bt_txt_heap (seqno = text, random = int4) \g create bt_f8_heap (seqno = float8, random = int4) \g /* * FUNCTION DEFINITIONS */ define function hobbies ( language = "postquel", returntype = setof hobbies_r ) arg is (person) as "retrieve (hobbies_r.all) where hobbies_r.person = $1.name" \g define function hobby_construct ( language = "postquel", returntype = hobbies_r ) arg is (text, text) as "retrieve (name = $1, hobby = $2)" \g define function equipment ( language = "postquel", returntype = setof equipment_r ) arg is (hobbies_r) as "retrieve (equipment_r.all) where equipment_r.hobby = $1.name" \g define function user_relns ( language = "postquel", returntype = setof char16 ) as "retrieve (pg_class.relname) where pg_class.relname !~ \\"pg_.*\\" and pg_class.relkind != 'i' " \g define function pt_in_circle ( language = "c", returntype = int4 ) arg is (point, circle) as "_OBJWD_/regress_SLSUFF_" \g define function overpaid ( language = "c", returntype = bool ) arg is (emp) as "_OBJWD_/regress_SLSUFF_" \g define function boxarea ( language = "c", returntype = int4 ) arg is (box) as "_OBJWD_/regress_SLSUFF_" \g define function interpt_pp ( language = "c", returntype = point ) arg is (path, path) as "_OBJWD_/regress_SLSUFF_" \g define function reverse_c16 ( language = "c", returntype = char16 ) arg is (char16) as "_OBJWD_/regress_SLSUFF_" \g /* untrusted functions */ define function ufp0 ( language = "c", returntype = int4 ) arg is ( ) as "_OBJWD_/ufp1_SLSUFF_" \g replace pg_proc ( proistrusted = "f"::bool, pronargs = 0 ) where pg_proc.proname = "ufp0" \g define function ufp1 ( language = "c", returntype = int4 ) arg is (int4) as "_OBJWD_/ufp1_SLSUFF_" \g replace pg_proc ( proistrusted = "f"::bool ) where pg_proc.proname = "ufp1" \g define function ufp2 ( language = "c", returntype = char16 ) arg is (char16, char16) as "_OBJWD_/ufp2_SLSUFF_" \g replace pg_proc ( proistrusted = "f"::bool ) where pg_proc.proname = "ufp2" \g define function ufp3 ( language = "c", returntype = text ) arg is (text, char) as "_OBJWD_/ufp3_SLSUFF_" \g replace pg_proc ( proistrusted = "f"::bool ) where pg_proc.proname = "ufp3" \g /* * FUNCTION DYNAMIC LOADING */ load "_OBJWD_/regress_SLSUFF_" \g /* * CLASS POPULATION * (any resemblance to real life is purely coincidental) */ copy onek from "_CWD_/../data/onek.data" \g copy tenk1 from "_CWD_/../data/tenk.data" \g append tenk2 (tenk1.all) \g retrieve into onek2 (onek.all) \g copy slow_emp4000 from "_CWD_/../data/rect.data" \g append fast_emp4000 (slow_emp4000.all) \g copy person from "_CWD_/../data/person.data" \g copy emp from "_CWD_/../data/emp.data" \g copy student from "_CWD_/../data/student.data" \g copy stud_emp from "_CWD_/../data/stud_emp.data" \g retrieve into Bprime (t.all) from t in tenk1 where t.unique2 < 1000 \g append hobbies_r (name = "posthacking", person = p.name) from p in person* where p.name = "mike" or p.name = "jeff" \g append hobbies_r (name = "basketball", person = person.name) where person.name = "joe" or person.name = "sally" \g append hobbies_r (name = "skywalking") \g append equipment_r (name = "advil", hobby = "posthacking") \g append equipment_r (name = "peet's coffee", hobby = "posthacking") \g append equipment_r (name = "hightops", hobby = "basketball") \g append equipment_r (name = "guts", hobby = "skywalking") \g copy road from "_CWD_/../data/streets.data" \g copy real_city from "_CWD_/../data/real_city.data" \g retrieve into ramp (road.all) where road.name ~ ".*Ramp" \g append ihighway (road.all) where road.name ~ "I- .*" \g append shighway (road.all) where road.name ~ "State Hwy.*" \g replace shighway (surface = "asphalt") \g append a_star (class = 'a', a = 1) \g append a_star (class = 'a', a = 2) \g append a_star (class = 'a') \g append b_star (class = 'b', a = 3, b = "mumble"::text) \g append b_star (class = 'b', a = 4) \g append b_star (class = 'b', b = "bumble"::text) \g append b_star (class = 'b') \g append c_star (class = 'c', a = 5, c = "hi mom"::char16) \g append c_star (class = 'c', a = 6) \g append c_star (class = 'c', c = "hi paul"::char16) \g append c_star (class = 'c') \g append d_star (class = 'd', a = 7, b = "grumble"::text, c = "hi sunita"::char16, d = "0.0"::float8) \g append d_star (class = 'd', a = 8, b = "stumble"::text, c = "hi koko"::char16) \g append d_star (class = 'd', a = 9, b = "rumble"::text, d = "1.1"::float8) \g append d_star (class = 'd', a = 10, c = "hi kristin"::char16, d = "10.01"::float8) \g append d_star (class = 'd', b = "crumble"::text, c = "hi boris"::char16, d = "100.001"::float8) \g append d_star (class = 'd', a = 11, b = "fumble"::text) \g append d_star (class = 'd', a = 12, c = "hi avi"::char16) \g append d_star (class = 'd', a = 13, d = "1000.0001"::float8) \g append d_star (class = 'd', b = "tumble"::text, c = "hi andrew"::char16) \g append d_star (class = 'd', b = "humble"::text, d = "10000.00001"::float8) \g append d_star (class = 'd', c = "hi ginger"::char16, d = "100000.000001"::float8) \g append d_star (class = 'd', a = 14) \g append d_star (class = 'd', b = "jumble"::text) \g append d_star (class = 'd', c = "hi jolly"::char16) \g append d_star (class = 'd', d = "1000000.0000001"::float8) \g append d_star (class = 'd') \g append e_star (class = 'e', a = 15, c = "hi carol"::char16, e = "-1"::int2) \g append e_star (class = 'e', a = 16, c = "hi bob"::char16) \g append e_star (class = 'e', a = 17, e = "-2"::int2) \g append e_star (class = 'e', c = "hi michelle"::char16, e = "-3"::int2) \g append e_star (class = 'e', a = 18) \g append e_star (class = 'e', c = "hi elisa"::char16) \g append e_star (class = 'e', e = "-4"::int2) \g append f_star (class = 'f', a = 19, c = "hi claire"::char16, e = "-5"::int2, f = "(1,2,3,4)"::polygon) \g append f_star (class = 'f', a = 20, c = "hi mike"::char16, e = "-6"::int2) \g append f_star (class = 'f', a = 21, c = "hi marcel"::char16, f = "(11,22,33,44,55,66)"::polygon) \g append f_star (class = 'f', a = 22, e = "-7"::int2, f = "(111,222,333,444,555,666,777,888)"::polygon) \g append f_star (class = 'f', c = "hi keith"::char16, e = "-8"::int2, f = "(1111,2222,3333,4444)"::polygon) \g append f_star (class = 'f', a = 24, c = "hi marc"::char16) \g append f_star (class = 'f', a = 25, e = "-9"::int2) \g append f_star (class = 'f', a = 26, f = "(11111,22222,33333,44444)"::polygon) \g append f_star (class = 'f', c = "hi allison"::char16, e = "-10"::int2) \g append f_star (class = 'f', c = "hi jeff"::char16, f = "(111111,222222,333333,444444)"::polygon) \g append f_star (class = 'f', e = "-11"::int2, f = "(1111111,2222222,3333333,4444444)"::polygon) \g append f_star (class = 'f', a = 27) \g append f_star (class = 'f', c = "hi carl"::char16) \g append f_star (class = 'f', e = "-12"::int2) \g append f_star (class = 'f', f = "(11111111,22222222,33333333,44444444)"::polygon) \g append f_star (class = 'f') \g copy hash_i4_heap from "_CWD_/../data/hash.data" \g copy hash_c16_heap from "_CWD_/../data/hash.data" \g copy hash_txt_heap from "_CWD_/../data/hash.data" \g copy hash_f8_heap from "_CWD_/../data/hash.data" \g copy bt_i4_heap from "_CWD_/../data/desc.data" \g copy bt_c16_heap from "_CWD_/../data/hash.data" \g copy bt_txt_heap from "_CWD_/../data/desc.data" \g copy bt_f8_heap from "_CWD_/../data/hash.data" \g /* * ARRAYS */ /* * only this array as a 0-based 'e', the others are 1-based. * 'e' is also a large object. */ append arrtest (a[5] = "{1,2,3,4,5}", b[2][1][2] = "{{{},{1,2}}}", c = "{}", d = "{}", e[0:4] = "/floatarr") \g replace arrtest (e[0] = "1.1") \g replace arrtest (e[1] = "2.2") \g append arrtest (a = "{11,12,23}", b[2][2][1] = "{{3,4},{4,5}}", c = "{"foobar"}", d = "{{"elt1", "elt2"}}", e = "{"3.4", "6.7"}") \g append arrtest (a = "{}", b[1][2][2] = "{3,4}", c = "{foo,bar}", d[2][1] = "{bar,foo}") \g create iportaltest (i = int4, d = float4, p = polygon) \g append iportaltest (i = 1, d = 3.567, p = "(3.0,4.0,1.0,2.0)"::polygon) \g append iportaltest (i = 2, d = 89.05, p = "(4.0,3.0,2.0,1.0)"::polygon) \g /* * Define ancillary data structures (i.e. indices) */ /* * BTREE */ define index onek_unique1 on onek using btree(unique1 int4_ops) \g define index onek_unique2 on onek using btree(unique2 int4_ops) \g define index onek_hundred on onek using btree(hundred int4_ops) \g define index onek_stringu1 on onek using btree(stringu1 char16_ops) \g define index tenk1_unique1 on tenk1 using btree(unique1 int4_ops) \g define index tenk1_unique2 on tenk1 using btree(unique2 int4_ops) \g define index tenk1_hundred on tenk1 using btree(hundred int4_ops) \g define index tenk2_unique1 on tenk2 using btree(unique1 int4_ops) \g define index tenk2_unique2 on tenk2 using btree(unique2 int4_ops) \g define index tenk2_hundred on tenk2 using btree(hundred int4_ops) \g define index rix on road using btree (name text_ops) \g define index iix on ihighway using btree (name text_ops) \g define index six on shighway using btree (name text_ops) \g /* * BTREE ascending/descending cases * * we load int4/text from pure descending data (each key is a new * low key) and c16/f8 from pure ascending data (each key is a new * high key). we had a bug where new low keys would sometimes be * "lost". */ define index bt_i4_index on bt_i4_heap using btree (seqno int4_ops) \g define index bt_c16_index on bt_c16_heap using btree (seqno char16_ops) \g define index bt_txt_index on bt_txt_heap using btree (seqno text_ops) \g define index bt_f8_index on bt_f8_heap using btree (seqno float8_ops) \g /* * BTREE partial indices */ define index onek2_u1_prtl on onek2 using btree(unique1 int4_ops) where onek2.unique1 < 20 or onek2.unique1 > 980 \g define index onek2_u2_prtl on onek2 using btree(unique2 int4_ops) where onek2.stringu1 < "B" \g extend index onek2_u2_prtl where onek2.stringu1 < "C" \g extend index onek2_u2_prtl \g define index onek2_stu1_prtl on onek2 using btree(stringu1 char16_ops) where onek2.stringu1 >= "J" and onek2.stringu1 < "K" \g /* * RTREE * * rtrees use a quadratic page-splitting algorithm that takes a * really, really long time. we don't test all rtree opclasses * in the regression test (we check them using the sequoia 2000 * benchmark). */ define index rect2ind on fast_emp4000 using rtree (home_base bigbox_ops) \g /* * HASH */ define index hash_i4_index on hash_i4_heap using hash (random int4_ops) \g define index hash_c16_index on hash_c16_heap using hash (random char16_ops) \g define index hash_txt_index on hash_txt_heap using hash (random text_ops) \g define index hash_f8_index on hash_f8_heap using hash (random float8_ops) \g /* * OPERATOR DEFINITIONS */ define operator ## ( arg1 = path, arg2 = path, procedure = path_inter, commutator = ## ) \g define operator <% ( arg1 = point, arg2 = circle, procedure = pt_in_circle, associativity = left, commutator = >=% ) \g define operator @#@ ( arg2 = int4, /* left unary */ associativity = right, procedure = int4fac ) \g define operator #@# ( arg1 = int4, /* right unary */ associativity = left, procedure = int4fac ) \g define operator #%# ( arg1 = int4, /* right unary */ procedure = int4fac ) \g /* * VIRTUAL CLASS DEFINITIONS * (this also tests the query rewrite system) */ define view street ( road.name, road.thepath, cname = real_city.cname ) where real_city.outline ## road.thepath \g define view iexit ( ihighway.name, ihighway.thepath, exit = interpt_pp(ihighway.thepath, ramp.thepath) ) where ihighway.thepath ## ramp.thepath \g define view toyemp ( emp.name, emp.age, emp.location, annualsal = 12*emp.salary ) \g /* * RULES */ /* * AGGREGATE DEFINITIONS */ /* all functions defined */ define aggregate newavg (sfunc1 = int4pl, basetype = int4, stype1 = int4, sfunc2 = int4inc, stype2 = int4, finalfunc = int4div, initcond1 = "0", initcond2 = "0") \g /* sfunc1 (value-dependent) only */ define aggregate newsum (sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond1 = "0") \g /* sfunc2 (value-independent) only */ define aggregate newcnt (sfunc2 = int4inc, stype2 = int4, initcond2 = "0") \g vacuum \g /* * sanity check, if we don't have indices the test will take years to * complete. */ retrieve ( pg_class.relname, pg_class.relhasindex ) where pg_class.relhasindex sort by relname \g