--Informix examples for Section 4.4

--Example 4.4.17
drop function sum_n;
create function sum_n(n int) returning int;
      define i int; 
      define total int;  
      let total = 0;
      for i = 1 to n
          let total = total + i;
      end for;
      return total;               -- return result to SQL or SPL caller
end function;

execute function sum_n(10);      

select sum_n(10) from orders;

--this causes numeric overflow on CAP orders table
select qty, sum_n(qty), 2*sum_n(qty*qty) from orders where aid = 'a04';

--Example 4.4.18
drop function namecity;

create function namecity(custid char(10)) returning char(20);
  define custname char(20);   -- make local string big enough (it gets trimmed below)
  define  custcity char(20);
  select cname, city into custname, custcity from customers where cid = custid;
  return trim(custname) || '(' || trim(custcity) || ')';  -- trim extra spaces off 
end function;  

--Example 4.4.19
drop function x_out;

create function x_out(s varchar(80)) returning varchar(80); 
      let s[1,3] = 'XXX';     -- change the parameter **DON'T DO THIS!!**
      return s;               -- return updated parameter variable
end function;  

select x_out(cname) from customers;          
drop function x_out;

create function x_out(s varchar(80)) returning varchar(80);
      define newstr varchar(80);
      let newstr = s;        -- clone parameter string to local variable newstr
      let newstr[1,3] = 'XXX';  -- change newstr's first three chars to 'XXX'
      return newstr;            -- return updated LOCAL string
end function;  



--Example 4.4.20
drop function inc_age;
create function inc_age(x person_t) returning person_t; 
      define nper person_t;          -- local variable nper
      let nper = cast(row(x.ssno, x.pname, x.age) as person_t);  -- clone x to nper
      let nper.age = nper.age + 1;   -- change age 
      return nper;           -- return local nper
end function;    

select inc_age(p) from people p;

--Example 4.4.21

drop function area;
drop function inside;
drop table rects;
drop table points;
drop row type rectangle_t restrict;  -- Note we must drop objects depending...
drop row type point_t restrict; -- ...on these types before dropping the types

create row type point_t 
(
      x int,            -- horizontal coordinate of point
      y int             -- vertical coordinate 
);
create row type rectangle_t 
(
      pt1 point_t,       -- point at lower left-hand corner of rectangle
      pt2 point_t        -- point at upper right-hand corner of rectangle
);

create function area (r rectangle_t) returning int; 
      return (r.pt2.x-r.pt1.x)*(r.pt2.y-r.pt1.y);  -- area of rectangle r
end function;

create function inside (r rectangle_t, p point_t) returning boolean;
      if (p.x >= r.pt1.x) and (p.x <= r.pt2.x) and (p.y >= r.pt1.y) and (p.y <= r.pt2.y)
          then return 't';     -- return True if p inside r
      else
          return 'f';          -- return False if p not inside r
      end if;
end function;

--Example 4.4.22
create table points of type point_t (primary key (x, y));
create table rects of type rectangle_t;
insert into rects values (cast(row(1,2) as point_t),cast(row(3,4) as point_t));
insert into rects values (cast(row(1,1) as point_t),cast(row(6,6) as point_t));
insert into points values (2,3);
insert into points values (1,4);
insert into points values (4,4);

select area(x) from rects x;
  select inside(x,cast(row(4,2) as point_t)) from rects x;

--Example 4.4.23
drop function subset;
create function subset(wdset1 set(varchar(30) not null), 
          wdset2 set(varchar(30) not null)) returning boolean;
      define matchcnt int;        -- local variable for count of word matchcnt
      define wd1 varchar(30);     -- local variable for word from wdset1
      define wd2 varchar(30);     -- local variable for word from wdset2
      foreach cursor1 for    -- loop through elements of wdset1; see Fig. 4.34
          select * into wd1 from table(wdset1) -- select 1 element at a time from cursor1
          let matchcnt = 0;       -- initialize count of matchcnt for wd1 in wdset2
          foreach cursor2 for     -- loop through elements of wdset2
              select * into wd2 from table(wdset2) 
              if (wd2 == wd1) then
                  let matchcnt = matchcnt + 1;  -- count match of wd1 in wdset2
              end if;
          end foreach;
          if (matchcnt==0) then
              return 'f';          -- this wd1 not in wdset2, fail
          end if;
      end foreach;
      return 't';           -- all wdset1 words were found in wdset2
end function;      

--Example 4.4.24
drop table documents;
drop row type document_t restrict; -- all objects dependent on type must be dropped
create row type document_t 
(
      docid int,   -- unique id of document
      docname varchar(40),   -- document title
      docauthor person_t,   -- document author
      keywords set (varchar(30) not null)  -- keyword set for document
);

create table documents of type document_t
(
     primary key(docid)
);  

--Example 4.4.25
select docid from documents
    where 'boat' in keywords; -- note IN doesn't require a Subquery in INFORMIX

select sum(cardinality(keywords)) from documents;      

--Example 4.4.26
select docid, docname from documents
      where subset(set{'boat','Atlantic','trip'},keywords);

select docid, docname from documents d
      where d.keywords = set{'boat','Atlantic','trip'};      

--Example 4.4.27
drop function addword;
create function addword(wdset set(varchar(30) not null), wd varchar(30)) 
          returning set(varchar(30) not null);
      define newset set(varchar(30) not null);
      let newset = wdset;         -- clone parameter set for return value
      insert into table(newset) values (wd);    -- add new element
      return newset;
end function;      

--Example 4.4.28
update documents set keywords = addword(keywords,'bird')
      where docid = 200;      

--Example 4.4.29
drop function delword;
create function delword(wdset set(varchar(30) not null),wd varchar(30))
          returning set(varchar(30) not null);
      define wd1 varchar(30);     -- for Select into use in cursor
      define newset set(varchar(30) not null);
      let newset = wdset;         -- clone set for return value
      foreach cursor1 for         -- scan set, looking for wd to delete
          select * into wd1 from table(newset) 
          if wd1 = wd then        -- found wd, delete it
              delete from table(newset) where current of cursor1;
              exit foreach;       -- only one to delete, so leave loop now
          end if;
      end foreach;
      return newset;         -- return set, possibly one element smaller
end function;
