--
-- pl-sql.sql 
-- 
-- created by philg on 11/18/98
--
-- useful pl/sql utility procedures 
--

-- MSB for some reason the top if isn't working
--     doesn't look like it is needed by this 
--     function, so i commented it out
create function logical_negation(varchar)
returns varchar as '
declare
	true_or_false alias for $1;
BEGIN
--  IF true_or_false is null THEN
--    return null;
  IF true_or_false = ''f'' THEN
    return ''t'';   
  ELSE 
    return ''f'';   
  END IF;
END;
' language 'plpgsql';

-- these come originally from the classified ads system
-- DRB: Ben's attempt didn't work, but this does.  Weird.

create function expired_p(datetime) returns boolean as '
declare
	expiration_date alias for $1;
begin
  return expiration_date <= current_timestamp;
end;
' language 'plpgsql';

-- DRB: These exploit the fact that subtracting dates returns
-- the number of days separating them as an integer.  Subtracting
-- datetimes returns a reltime of the form "'n days'", not what
-- the calling scripts expect.

create function days_since_posted(date) returns integer as '
declare
  posted alias for $1;
begin
	return current_timestamp::date - posted;
end;
' language 'plpgsql';


-- useful for ecommerce and other situations where you want to
-- know whether something happened within last N days (assumes query_date
-- is in the past)

create function one_if_within_n_days (datetime, integer)
returns integer as '
declare
	query_date alias for $1;
	n_days alias for $2;
begin
  IF current_timestamp::date - query_date::date <= n_days THEN 
    return 1;
  ELSE
    return 0;
  END IF;
end;
' language 'plpgsql';

--drop function pseudo_contains( varchar, varchar) ;
create function pseudo_contains (varchar, varchar)
returns integer as '
declare
	indexed_stuff alias for $1;
	space_sep_list_untrimmed alias for $2;
  space_sep_list        text;
  upper_indexed_stuff   text;
  -- if you call this var START you get hosed royally
  first_space           integer;
  score                 integer;
BEGIN 
  space_sep_list := upper(ltrim(rtrim(space_sep_list_untrimmed)));
  upper_indexed_stuff := upper(indexed_stuff);
  score := 0;
  IF space_sep_list is null or indexed_stuff is null THEN
    RETURN score;  
  END IF;
  LOOP
   first_space := position('' '' in space_sep_list);
   IF first_space = 0 THEN
     -- one token or maybe end of list
     IF position(space_sep_list in upper_indexed_stuff) <> 0 THEN
        RETURN score+10;
     END IF;
     RETURN score;
   ELSE
   -- first_space <> 0
     IF position(substring(space_sep_list from 1 to first_space-1) in upper_indexed_stuff) <> 0 THEN
        score := score + 10;
     END IF;
   END IF;
    space_sep_list := substring(space_sep_list from first_space+1);
  END LOOP;  
END;
' language 'plpgsql';

--drop function add_months (datetime, integer) ;
create function add_months (datetime, integer) returns datetime as '
declare
  base alias for $1;
  months alias for $2;
begin
  return base + (months || ''months'')::timespan;
end;
' language 'plpgsql';

-- Returns the interval in fractional days between two dates.
-- Useful especially in ecommerce where there are checks for
-- 0.95 parts of a day, etc.

create function date_interval(datetime, datetime)
 returns float as '
declare
  later alias for $1;
  earlier alias for $2;
begin
  return
    (date_part(''epoch'', later)-date_part(''epoch'', earlier))/86400.0;
end;
' language 'plpgsql';

-- useful for working around outer joins involving users email.
-- ticket tracker needed it, decided to make it non-specific as
-- other modules probably do, too.

create function email_or_null(integer) returns varchar
as '
begin
  return email from users where user_id = $1;
end;
' language 'plpgsql';

create function to_date_or_null(datetime) returns date
as '
begin
  if $1 is null
  then return null;
  end if;
  return $1::date;
end;
' language 'plpgsql';