1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('2.0.10', 2, 0, 10))

5 AS "subquery"("string", "major", "minor", "revision");

6

7 DROP FUNCTION "delegation_info"

8 ( "member"."id"%TYPE,

9 "unit"."id"%TYPE,

10 "area"."id"%TYPE,

11 "issue"."id"%TYPE,

12 "member"."id"%TYPE );

13

14 DROP TYPE "delegation_info_type";

15

16

17 CREATE TYPE "delegation_info_type" AS (

18 "own_participation" BOOLEAN,

19 "own_delegation_scope" "delegation_scope",

20 "first_trustee_id" INT4,

21 "first_trustee_participation" BOOLEAN,

22 "first_trustee_ellipsis" BOOLEAN,

23 "other_trustee_id" INT4,

24 "other_trustee_participation" BOOLEAN,

25 "other_trustee_ellipsis" BOOLEAN,

26 "delegation_loop" "delegation_info_loop_type",

27 "participating_member_id" INT4 );

28

29 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';

30

31 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';

32 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';

33 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';

34 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';

35 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';

36 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';

37 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';

38 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';

39 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';

40 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';

41

42

43 CREATE FUNCTION "delegation_info"

44 ( "member_id_p" "member"."id"%TYPE,

45 "unit_id_p" "unit"."id"%TYPE,

46 "area_id_p" "area"."id"%TYPE,

47 "issue_id_p" "issue"."id"%TYPE,

48 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )

49 RETURNS "delegation_info_type"

50 LANGUAGE 'plpgsql' STABLE AS $$

51 DECLARE

52 "current_row" "delegation_chain_row";

53 "result" "delegation_info_type";

54 BEGIN

55 "result"."own_participation" := FALSE;

56 FOR "current_row" IN

57 SELECT * FROM "delegation_chain"(

58 "member_id_p",

59 "unit_id_p", "area_id_p", "issue_id_p",

60 "simulate_trustee_id_p")

61 LOOP

62 IF

63 "result"."participating_member_id" ISNULL AND

64 "current_row"."participation"

65 THEN

66 "result"."participating_member_id" := "current_row"."member_id";

67 END IF;

68 IF "current_row"."member_id" = "member_id_p" THEN

69 "result"."own_participation" := "current_row"."participation";

70 "result"."own_delegation_scope" := "current_row"."scope_out";

71 IF "current_row"."loop" = 'first' THEN

72 "result"."delegation_loop" := 'own';

73 END IF;

74 ELSIF

75 "current_row"."member_valid" AND

76 ( "current_row"."loop" ISNULL OR

77 "current_row"."loop" != 'repetition' )

78 THEN

79 IF "result"."first_trustee_id" ISNULL THEN

80 "result"."first_trustee_id" := "current_row"."member_id";

81 "result"."first_trustee_participation" := "current_row"."participation";

82 "result"."first_trustee_ellipsis" := FALSE;

83 IF "current_row"."loop" = 'first' THEN

84 "result"."delegation_loop" := 'first';

85 END IF;

86 ELSIF "result"."other_trustee_id" ISNULL THEN

87 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN

88 "result"."other_trustee_id" := "current_row"."member_id";

89 "result"."other_trustee_participation" := TRUE;

90 "result"."other_trustee_ellipsis" := FALSE;

91 IF "current_row"."loop" = 'first' THEN

92 "result"."delegation_loop" := 'other';

93 END IF;

94 ELSE

95 "result"."first_trustee_ellipsis" := TRUE;

96 IF "current_row"."loop" = 'first' THEN

97 "result"."delegation_loop" := 'first_ellipsis';

98 END IF;

99 END IF;

100 ELSE

101 "result"."other_trustee_ellipsis" := TRUE;

102 IF "current_row"."loop" = 'first' THEN

103 "result"."delegation_loop" := 'other_ellipsis';

104 END IF;

105 END IF;

106 END IF;

107 END LOOP;

108 RETURN "result";

109 END;

110 $$;

111

112 COMMENT ON FUNCTION "delegation_info"

113 ( "member"."id"%TYPE,

114 "unit"."id"%TYPE,

115 "area"."id"%TYPE,

116 "issue"."id"%TYPE,

117 "member"."id"%TYPE )

118 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';

119

120

121 COMMIT;