darcs - gombemi

2 dnl

3 dnl This file is part of Gombe-MI.

4 dnl

5 dnl Gombe-MI is free software; you can redistribute it and/or modify

6 dnl it under the terms of the GNU General Public License as published by

7 dnl the Free Software Foundation; either version 3 of the License, or

8 dnl (at your option) any later version.

9 dnl

10 dnl This program is distributed in the hope that it will be useful,

11 dnl but WITHOUT ANY WARRANTY; without even the implied warranty of

12 dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

13 dnl GNU General Public License for more details.

14 dnl

15 dnl You should have received a copy of the GNU General Public License

16 dnl along with Gombe-MI. If not, see <http://www.gnu.org/licenses/>.

17 dnl

18 dnl Function to convert an animid in the access db.

19 dnl

20 dnl Karl O. Pinc <kop@meme.com>

22 dnl m4 includes

23 include(`copyright.m4')

24 include(`constants.m4')

25 include(`types.m4')

26 include(`macros.m4')dnl

28 dnl Reset our search path so we pick up the conversion schema.

29 define(`gmi_function_search_path', `prepared_data, gombemi, pg_temp')

31 CREATE OR REPLACE FUNCTION prepared_data.convert_animid(this_animid VARCHAR(gmi_animid_len)

32 , this_date DATE)

33 RETURNS VARCHAR(gmi_animid_len)

34 LANGUAGE plpgsql

35 IMMUTABLE

36 SECURITY DEFINER

37 gmi_function_set_search_path

38 AS $$

39 -- Convert a mis-identified animid.

40 --

41 -- GPL_notice(` --', `2013', `The Meme Factory, Inc. http://www.meme.com/')

42 --

43 -- Syntax: convert_animid(this_animid, this_date);

44 --

45 -- Arguments:

46 -- this_animid The animid to convert

47 -- this_date Date of the data in question.

48 --

49 -- Returns:

50 -- The right animid.

51 --

52 -- Remarks:

53 -- One-off program. Once it works we won't run it again.

55 DECLARE

56 BEGIN

57 CASE this_animid

58 WHEN 'CN' THEN

59 IF this_date >= '2001-12-02' AND this_date <= '2004-12-31' THEN

60 RETURN 'BAH';

61 END IF;

62 WHEN 'CF' THEN

63 IF this_date >= '1995-03-02' THEN

64 IF this_date <= '2001-12-01' THEN

65 RETURN 'UNK';

66 ELSIF this_date <= '2004-12-31' THEN

67 RETURN 'BAH';

68 END IF;

69 END IF;

70 WHEN 'TT' THEN

71 IF this_date >= '1998-02-06' AND this_date <= '2008-03-02' THEN

72 RETURN 'TTA';

73 END IF;

74 WHEN 'WD' THEN

75 IF this_date >= '2001-01-01' AND this_date <= '2013-07-02' THEN

76 RETURN 'SIF';

77 END IF;

78 ELSE

79 NULL;

80 END CASE;

82 RETURN this_animid;

84 END;

85 $$;

88 grant_func_priv(`prepared_data.convert_animid(VARCHAR(gmi_animid_len), DATE)')