rev |
line source |
jbe@253
|
1 BEGIN;
|
jbe@253
|
2
|
jbe@253
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@253
|
4 SELECT * FROM (VALUES ('2.0.10', 2, 0, 10))
|
jbe@253
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@253
|
6
|
jbe@253
|
7 DROP FUNCTION "delegation_info"
|
jbe@253
|
8 ( "member"."id"%TYPE,
|
jbe@253
|
9 "unit"."id"%TYPE,
|
jbe@253
|
10 "area"."id"%TYPE,
|
jbe@253
|
11 "issue"."id"%TYPE,
|
jbe@253
|
12 "member"."id"%TYPE );
|
jbe@253
|
13
|
jbe@253
|
14 DROP TYPE "delegation_info_type";
|
jbe@253
|
15
|
jbe@253
|
16
|
jbe@253
|
17 CREATE TYPE "delegation_info_type" AS (
|
jbe@253
|
18 "own_participation" BOOLEAN,
|
jbe@253
|
19 "own_delegation_scope" "delegation_scope",
|
jbe@253
|
20 "first_trustee_id" INT4,
|
jbe@253
|
21 "first_trustee_participation" BOOLEAN,
|
jbe@253
|
22 "first_trustee_ellipsis" BOOLEAN,
|
jbe@253
|
23 "other_trustee_id" INT4,
|
jbe@253
|
24 "other_trustee_participation" BOOLEAN,
|
jbe@253
|
25 "other_trustee_ellipsis" BOOLEAN,
|
jbe@253
|
26 "delegation_loop" "delegation_info_loop_type",
|
jbe@253
|
27 "participating_member_id" INT4 );
|
jbe@253
|
28
|
jbe@253
|
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';
|
jbe@253
|
30
|
jbe@253
|
31 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
|
jbe@253
|
32 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
|
jbe@253
|
33 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
|
jbe@253
|
34 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
|
jbe@253
|
35 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
|
jbe@253
|
36 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
|
jbe@253
|
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)';
|
jbe@253
|
38 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
|
jbe@253
|
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';
|
jbe@253
|
40 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
|
jbe@253
|
41
|
jbe@253
|
42
|
jbe@253
|
43 CREATE FUNCTION "delegation_info"
|
jbe@253
|
44 ( "member_id_p" "member"."id"%TYPE,
|
jbe@253
|
45 "unit_id_p" "unit"."id"%TYPE,
|
jbe@253
|
46 "area_id_p" "area"."id"%TYPE,
|
jbe@253
|
47 "issue_id_p" "issue"."id"%TYPE,
|
jbe@253
|
48 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
|
jbe@253
|
49 RETURNS "delegation_info_type"
|
jbe@253
|
50 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@253
|
51 DECLARE
|
jbe@253
|
52 "current_row" "delegation_chain_row";
|
jbe@253
|
53 "result" "delegation_info_type";
|
jbe@253
|
54 BEGIN
|
jbe@253
|
55 "result"."own_participation" := FALSE;
|
jbe@253
|
56 FOR "current_row" IN
|
jbe@253
|
57 SELECT * FROM "delegation_chain"(
|
jbe@253
|
58 "member_id_p",
|
jbe@253
|
59 "unit_id_p", "area_id_p", "issue_id_p",
|
jbe@253
|
60 "simulate_trustee_id_p")
|
jbe@253
|
61 LOOP
|
jbe@253
|
62 IF
|
jbe@253
|
63 "result"."participating_member_id" ISNULL AND
|
jbe@253
|
64 "current_row"."participation"
|
jbe@253
|
65 THEN
|
jbe@253
|
66 "result"."participating_member_id" := "current_row"."member_id";
|
jbe@253
|
67 END IF;
|
jbe@253
|
68 IF "current_row"."member_id" = "member_id_p" THEN
|
jbe@253
|
69 "result"."own_participation" := "current_row"."participation";
|
jbe@253
|
70 "result"."own_delegation_scope" := "current_row"."scope_out";
|
jbe@253
|
71 IF "current_row"."loop" = 'first' THEN
|
jbe@253
|
72 "result"."delegation_loop" := 'own';
|
jbe@253
|
73 END IF;
|
jbe@253
|
74 ELSIF
|
jbe@253
|
75 "current_row"."member_valid" AND
|
jbe@253
|
76 ( "current_row"."loop" ISNULL OR
|
jbe@253
|
77 "current_row"."loop" != 'repetition' )
|
jbe@253
|
78 THEN
|
jbe@253
|
79 IF "result"."first_trustee_id" ISNULL THEN
|
jbe@253
|
80 "result"."first_trustee_id" := "current_row"."member_id";
|
jbe@253
|
81 "result"."first_trustee_participation" := "current_row"."participation";
|
jbe@253
|
82 "result"."first_trustee_ellipsis" := FALSE;
|
jbe@253
|
83 IF "current_row"."loop" = 'first' THEN
|
jbe@253
|
84 "result"."delegation_loop" := 'first';
|
jbe@253
|
85 END IF;
|
jbe@253
|
86 ELSIF "result"."other_trustee_id" ISNULL THEN
|
jbe@253
|
87 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
|
jbe@253
|
88 "result"."other_trustee_id" := "current_row"."member_id";
|
jbe@253
|
89 "result"."other_trustee_participation" := TRUE;
|
jbe@253
|
90 "result"."other_trustee_ellipsis" := FALSE;
|
jbe@253
|
91 IF "current_row"."loop" = 'first' THEN
|
jbe@253
|
92 "result"."delegation_loop" := 'other';
|
jbe@253
|
93 END IF;
|
jbe@253
|
94 ELSE
|
jbe@253
|
95 "result"."first_trustee_ellipsis" := TRUE;
|
jbe@253
|
96 IF "current_row"."loop" = 'first' THEN
|
jbe@253
|
97 "result"."delegation_loop" := 'first_ellipsis';
|
jbe@253
|
98 END IF;
|
jbe@253
|
99 END IF;
|
jbe@253
|
100 ELSE
|
jbe@253
|
101 "result"."other_trustee_ellipsis" := TRUE;
|
jbe@253
|
102 IF "current_row"."loop" = 'first' THEN
|
jbe@253
|
103 "result"."delegation_loop" := 'other_ellipsis';
|
jbe@253
|
104 END IF;
|
jbe@253
|
105 END IF;
|
jbe@253
|
106 END IF;
|
jbe@253
|
107 END LOOP;
|
jbe@253
|
108 RETURN "result";
|
jbe@253
|
109 END;
|
jbe@253
|
110 $$;
|
jbe@253
|
111
|
jbe@253
|
112 COMMENT ON FUNCTION "delegation_info"
|
jbe@253
|
113 ( "member"."id"%TYPE,
|
jbe@253
|
114 "unit"."id"%TYPE,
|
jbe@253
|
115 "area"."id"%TYPE,
|
jbe@253
|
116 "issue"."id"%TYPE,
|
jbe@253
|
117 "member"."id"%TYPE )
|
jbe@253
|
118 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
|
jbe@253
|
119
|
jbe@253
|
120
|
jbe@253
|
121 COMMIT;
|