author | Pascal Volk <neverseen@users.sourceforge.net> |
Tue, 01 Nov 2011 02:13:57 +0000 | |
branch | v0.6.x |
changeset 441 | 1af5fe0683ce |
parent 437 | 9823548b2717 |
child 485 | 10e9b4855173 |
permissions | -rw-r--r-- |
72
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
1 |
-- --- Information: |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
2 |
-- This file contains some data types and functions these should speed up some |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
3 |
-- operations. Read the comment on each data type/functions for more details. |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
4 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
5 |
|
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
6 |
-- --- |
73
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
7 |
-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) |
72
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
8 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
9 |
CREATE TYPE sender_login AS ( |
73
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
10 |
sender varchar(320), |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
11 |
login text |
72
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
12 |
); |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
13 |
|
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
14 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
15 |
-- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
16 |
-- varchar localpart |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
17 |
-- varchar the_domain |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
18 |
-- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM): |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
19 |
-- set of sender_login records. |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
20 |
-- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
21 |
-- Required access privileges for your postfix database user: |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
22 |
-- GRANT SELECT ON domain_name, users, alias TO postfix; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
23 |
-- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
24 |
-- For more details see postconf(5) section smtpd_sender_login_maps |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
25 |
-- --- |
73
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
26 |
CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map( |
72
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
27 |
IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
28 |
AS $$ |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
29 |
DECLARE |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
30 |
rec sender_login; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
31 |
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
32 |
sender varchar(320) := localpart || '@' || the_domain; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
33 |
BEGIN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
34 |
-- Get all addresses for 'localpart' in the primary and aliased domains |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
35 |
FOR rec IN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
36 |
SELECT sender, local_part || '@' || domainname |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
37 |
FROM domain_name, users |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
38 |
WHERE domain_name.gid = did |
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
39 |
AND users.gid = did |
72
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
40 |
AND users.local_part = localpart |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
41 |
LOOP |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
42 |
RETURN NEXT rec; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
43 |
END LOOP; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
44 |
IF NOT FOUND THEN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
45 |
-- Loop over the alias addresses for localpart@the_domain |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
46 |
FOR rec IN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
47 |
SELECT DISTINCT sender, destination |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
48 |
FROM alias |
82
6c85915f3815
„speedup commit“ ;-)
Pascal Volk <neverseen@users.sourceforge.net>
parents:
75
diff
changeset
|
49 |
WHERE alias.gid = did |
6c85915f3815
„speedup commit“ ;-)
Pascal Volk <neverseen@users.sourceforge.net>
parents:
75
diff
changeset
|
50 |
AND alias.address = localpart |
72
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
51 |
LOOP |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
52 |
RETURN NEXT rec; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
53 |
END LOOP; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
54 |
END IF; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
55 |
RETURN; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
56 |
END; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
57 |
$$ LANGUAGE plpgsql STABLE |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
58 |
RETURNS NULL ON NULL INPUT |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
59 |
EXTERNAL SECURITY INVOKER; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
60 |
|
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
61 |
-- ########################################################################## -- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
62 |
|
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
63 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
64 |
-- Data type for function postfix_virtual_mailbox(varchar, varchar) |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
65 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
66 |
CREATE TYPE address_maildir AS ( |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
67 |
address varchar(320), |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
68 |
maildir text |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
69 |
); |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
70 |
|
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
71 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
72 |
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
73 |
-- varchar localpart |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
74 |
-- varchar the_domain |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
75 |
-- Returns: address_maildir records |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
76 |
-- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
77 |
-- Required access privileges for your postfix database user: |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
78 |
-- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
79 |
-- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
80 |
-- For more details see postconf(5) section virtual_mailbox_maps |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
81 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
82 |
CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
83 |
IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
84 |
AS $$ |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
85 |
DECLARE |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
86 |
rec address_maildir; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
87 |
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
88 |
address varchar(320) := localpart || '@' || the_domain; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
89 |
BEGIN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
90 |
FOR rec IN |
297
e21ceaabe871
pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
160
diff
changeset
|
91 |
SELECT address, domaindir||'/'||users.uid||'/'||directory||'/' |
72
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
92 |
FROM domain_data, users, maillocation |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
93 |
WHERE domain_data.gid = did |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
94 |
AND users.gid = did |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
95 |
AND users.local_part = localpart |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
96 |
AND maillocation.mid = users.mid |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
97 |
LOOP |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
98 |
RETURN NEXT rec; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
99 |
END LOOP; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
100 |
RETURN; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
101 |
END; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
102 |
$$ LANGUAGE plpgsql STABLE |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
103 |
RETURNS NULL ON NULL INPUT |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
104 |
EXTERNAL SECURITY INVOKER; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
105 |
|
73
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
106 |
-- ########################################################################## -- |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
107 |
|
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
108 |
-- --- |
74
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
109 |
-- Data type for functions: postfix_relocated_map(varchar, varchar) |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
110 |
-- postfix_virtual_alias_map(varchar, varchar) |
73
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
111 |
-- --- |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
112 |
CREATE TYPE recipient_destination AS ( |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
113 |
recipient varchar(320), |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
114 |
destination text |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
115 |
); |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
116 |
|
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
117 |
-- --- |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
118 |
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
119 |
-- varchar localpart |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
120 |
-- varchar the_domain |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
121 |
-- Returns: recipient_destination records |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
122 |
-- |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
123 |
-- Required access privileges for your postfix database user: |
74
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
124 |
-- GRANT SELECT ON alias, domain_name TO postfix; |
73
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
125 |
-- |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
126 |
-- For more details see postconf(5) section virtual_alias_maps and virtual(5) |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
127 |
-- --- |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
128 |
CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
129 |
IN localpart varchar, IN the_domain varchar) |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
130 |
RETURNS SETOF recipient_destination |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
131 |
AS $$ |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
132 |
DECLARE |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
133 |
record recipient_destination; |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
134 |
recipient varchar(320) := localpart || '@' || the_domain; |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
135 |
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
136 |
BEGIN |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
137 |
FOR record IN |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
138 |
SELECT recipient, destination |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
139 |
FROM alias |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
140 |
WHERE gid = did |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
141 |
AND address = localpart |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
142 |
LOOP |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
143 |
RETURN NEXT record; |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
144 |
END LOOP; |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
145 |
RETURN; |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
146 |
END; |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
147 |
$$ LANGUAGE plpgsql STABLE |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
148 |
RETURNS NULL ON NULL INPUT |
11da3d9298b4
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
72
diff
changeset
|
149 |
EXTERNAL SECURITY INVOKER; |
74
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
150 |
|
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
151 |
-- ########################################################################## -- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
152 |
|
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
153 |
-- --- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
154 |
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
155 |
-- varchar localpart |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
156 |
-- varchar the_domain |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
157 |
-- Returns: recipient_destination records |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
158 |
-- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
159 |
-- Required access privileges for your postfix database user: |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
160 |
-- GRANT SELECT ON domain_name, relocated TO postfix; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
161 |
-- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
162 |
-- For more details see postconf(5) section relocated_maps and relocated(5) |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
163 |
-- --- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
164 |
CREATE OR REPLACE FUNCTION postfix_relocated_map( |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
165 |
IN localpart varchar, IN the_domain varchar) |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
166 |
RETURNS SETOF recipient_destination |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
167 |
AS $$ |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
168 |
DECLARE |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
169 |
record recipient_destination; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
170 |
recipient varchar(320) := localpart || '@' || the_domain; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
171 |
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
172 |
BEGIN |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
173 |
FOR record IN |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
174 |
SELECT recipient, destination |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
175 |
FROM relocated |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
176 |
WHERE gid = did |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
177 |
AND address = localpart |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
178 |
LOOP |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
179 |
RETURN NEXT record; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
180 |
END LOOP; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
181 |
RETURN; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
182 |
END; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
183 |
$$ LANGUAGE plpgsql STABLE |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
184 |
RETURNS NULL ON NULL INPUT |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
185 |
EXTERNAL SECURITY INVOKER; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
186 |
|
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
187 |
-- ########################################################################## -- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
188 |
|
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
189 |
-- --- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
190 |
-- Data type for function postfix_transport_map(varchar, varchar) |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
191 |
-- --- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
192 |
CREATE TYPE recipient_transport AS ( |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
193 |
recipient varchar(320), |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
194 |
transport text |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
195 |
); |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
196 |
|
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
197 |
-- --- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
198 |
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
199 |
-- varchar localpart |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
200 |
-- varchar the_domain |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
201 |
-- Returns: recipient_transport records |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
202 |
-- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
203 |
-- Required access privileges for your postfix database user: |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
204 |
-- GRANT SELECT ON users, transport, domain_name TO postfix; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
205 |
-- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
206 |
-- For more details see postconf(5) section transport_maps and transport(5) |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
207 |
-- --- |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
208 |
CREATE OR REPLACE FUNCTION postfix_transport_map( |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
209 |
IN localpart varchar, IN the_domain varchar) |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
210 |
RETURNS SETOF recipient_transport |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
211 |
AS $$ |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
212 |
DECLARE |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
213 |
record recipient_transport; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
214 |
recipient varchar(320) := localpart || '@' || the_domain; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
215 |
BEGIN |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
216 |
FOR record IN |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
217 |
SELECT recipient, transport |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
218 |
FROM transport |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
219 |
WHERE tid = (SELECT tid |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
220 |
FROM users |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
221 |
WHERE gid = (SELECT gid |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
222 |
FROM domain_name |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
223 |
WHERE domainname = the_domain) |
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
224 |
AND local_part = localpart) |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
225 |
LOOP |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
226 |
RETURN NEXT record; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
227 |
END LOOP; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
228 |
RETURN; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
229 |
END; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
230 |
$$ LANGUAGE plpgsql STABLE |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
231 |
RETURNS NULL ON NULL INPUT |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
232 |
EXTERNAL SECURITY INVOKER; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
233 |
|
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
234 |
-- ########################################################################## -- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
235 |
|
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
236 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
237 |
-- Data type for function postfix_virtual_uid_map(varchar, varchar) |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
238 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
239 |
CREATE TYPE recipient_uid AS ( |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
240 |
recipient varchar(320), |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
241 |
uid bigint |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
242 |
); |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
243 |
|
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
244 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
245 |
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
246 |
-- varchar localpart |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
247 |
-- varchar the_domain |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
248 |
-- Returns: recipient_uid records |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
249 |
-- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
250 |
-- Required access privileges for your postfix database user: |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
251 |
-- GRANT SELECT ON users, domain_name TO postfix; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
252 |
-- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
253 |
-- For more details see postconf(5) section virtual_uid_maps |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
254 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
255 |
CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
256 |
IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
257 |
AS $$ |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
258 |
DECLARE |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
259 |
record recipient_uid; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
260 |
recipient varchar(320) := localpart || '@' || the_domain; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
261 |
BEGIN |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
262 |
FOR record IN |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
263 |
SELECT recipient, uid |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
264 |
FROM users |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
265 |
WHERE gid = (SELECT gid |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
266 |
FROM domain_name |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
267 |
WHERE domainname = the_domain) |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
268 |
AND local_part = localpart |
74
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
269 |
LOOP |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
270 |
RETURN NEXT record; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
271 |
END LOOP; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
272 |
RETURN; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
273 |
END; |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
274 |
$$ LANGUAGE plpgsql STABLE |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
275 |
RETURNS NULL ON NULL INPUT |
67a454ea5472
'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
73
diff
changeset
|
276 |
EXTERNAL SECURITY INVOKER; |
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
277 |
|
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
278 |
-- ########################################################################## -- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
279 |
|
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
280 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
281 |
-- Data type for function dovecotuser(varchar, varchar) |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
282 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
283 |
CREATE TYPE dovecotuser AS ( |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
284 |
userid varchar(320), |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
285 |
uid bigint, |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
286 |
gid bigint, |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
287 |
home text, |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
288 |
mail text |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
289 |
); |
382
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
290 |
-- --- |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
291 |
-- Data type for function dovecotquotauser(varchar, varchar) |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
292 |
-- --- |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
293 |
CREATE TYPE dovecotquotauser AS ( |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
294 |
userid varchar(320), |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
295 |
uid bigint, |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
296 |
gid bigint, |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
297 |
home text, |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
298 |
mail text, |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
299 |
quota_rule text |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
300 |
); |
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
301 |
|
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
302 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
303 |
-- Parameters (from login name [localpart@the_domain]): |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
304 |
-- varchar localpart |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
305 |
-- varchar the_domain |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
306 |
-- Returns: dovecotuser records |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
307 |
-- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
308 |
-- Required access privileges for your dovecot database user: |
297
e21ceaabe871
pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
160
diff
changeset
|
309 |
-- GRANT SELECT |
e21ceaabe871
pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
160
diff
changeset
|
310 |
-- ON users, domain_data, domain_name, maillocation, mailboxformat |
e21ceaabe871
pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
160
diff
changeset
|
311 |
-- TO dovecot; |
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
312 |
-- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
313 |
-- For more details see http://wiki.dovecot.org/UserDatabase |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
314 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
315 |
CREATE OR REPLACE FUNCTION dovecotuser( |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
316 |
IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
317 |
AS $$ |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
318 |
DECLARE |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
319 |
record dovecotuser; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
320 |
userid varchar(320) := localpart || '@' || the_domain; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
321 |
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
322 |
BEGIN |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
323 |
FOR record IN |
297
e21ceaabe871
pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
160
diff
changeset
|
324 |
SELECT userid, uid, did, domaindir || '/' || uid AS home, |
e21ceaabe871
pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
160
diff
changeset
|
325 |
format || ':~/' || directory AS mail |
e21ceaabe871
pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
160
diff
changeset
|
326 |
FROM users, domain_data, mailboxformat, maillocation |
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
327 |
WHERE users.gid = did |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
328 |
AND users.local_part = localpart |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
329 |
AND maillocation.mid = users.mid |
297
e21ceaabe871
pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
160
diff
changeset
|
330 |
AND mailboxformat.fid = maillocation.fid |
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
331 |
AND domain_data.gid = did |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
332 |
LOOP |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
333 |
RETURN NEXT record; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
334 |
END LOOP; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
335 |
RETURN; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
336 |
END; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
337 |
$$ LANGUAGE plpgsql STABLE |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
338 |
RETURNS NULL ON NULL INPUT |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
339 |
EXTERNAL SECURITY INVOKER; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
340 |
|
382
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
341 |
-- --- |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
342 |
-- Nearly the same as function dovecotuser above. It returns additionally the |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
343 |
-- field quota_rule. |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
344 |
-- |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
345 |
-- Required access privileges for your dovecot database user: |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
346 |
-- GRANT SELECT |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
347 |
-- ON users, domain_data, domain_name, maillocation, mailboxformat, |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
348 |
-- quotalimit |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
349 |
-- TO dovecot; |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
350 |
-- --- |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
351 |
CREATE OR REPLACE FUNCTION dovecotquotauser( |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
352 |
IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
353 |
AS $$ |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
354 |
DECLARE |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
355 |
record dovecotquotauser; |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
356 |
userid varchar(320) := localpart || '@' || the_domain; |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
357 |
did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
358 |
BEGIN |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
359 |
FOR record IN |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
360 |
SELECT userid, uid, did, domaindir || '/' || uid AS home, |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
361 |
format || ':~/' || directory AS mail, '*:bytes=' || |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
362 |
bytes || ':messages=' || messages AS quota_rule |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
363 |
FROM users, domain_data, mailboxformat, maillocation, quotalimit |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
364 |
WHERE users.gid = did |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
365 |
AND users.local_part = localpart |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
366 |
AND maillocation.mid = users.mid |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
367 |
AND mailboxformat.fid = maillocation.fid |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
368 |
AND domain_data.gid = did |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
369 |
AND quotalimit.qid = users.qid |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
370 |
LOOP |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
371 |
RETURN NEXT record; |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
372 |
END LOOP; |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
373 |
RETURN; |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
374 |
END; |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
375 |
$$ LANGUAGE plpgsql STABLE |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
376 |
RETURNS NULL ON NULL INPUT |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
377 |
EXTERNAL SECURITY INVOKER; |
5e6bcb2e010e
pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
297
diff
changeset
|
378 |
|
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
379 |
-- ########################################################################## -- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
380 |
|
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
381 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
382 |
-- Data type for function dovecotpassword(varchar, varchar) |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
383 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
384 |
CREATE TYPE dovecotpassword AS ( |
124
68af38212ff5
Added create SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
102
diff
changeset
|
385 |
userid varchar(320), |
297
e21ceaabe871
pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
160
diff
changeset
|
386 |
password varchar(270), |
124
68af38212ff5
Added create SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
102
diff
changeset
|
387 |
smtp boolean, |
68af38212ff5
Added create SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
102
diff
changeset
|
388 |
pop3 boolean, |
68af38212ff5
Added create SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
102
diff
changeset
|
389 |
imap boolean, |
68af38212ff5
Added create SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
102
diff
changeset
|
390 |
sieve boolean |
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
391 |
); |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
392 |
|
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
393 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
394 |
-- Parameters (from login name [localpart@the_domain]): |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
395 |
-- varchar localpart |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
396 |
-- varchar the_domain |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
397 |
-- Returns: dovecotpassword records |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
398 |
-- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
399 |
-- Required access privileges for your dovecot database user: |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
400 |
-- GRANT SELECT ON users, domain_name TO dovecot; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
401 |
-- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
402 |
-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
403 |
-- --- |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
404 |
CREATE OR REPLACE FUNCTION dovecotpassword( |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
405 |
IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
406 |
AS $$ |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
407 |
DECLARE |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
408 |
record dovecotpassword; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
409 |
userid varchar(320) := localpart || '@' || the_domain; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
410 |
BEGIN |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
411 |
FOR record IN |
124
68af38212ff5
Added create SQL scripts for Dovecot v1.2.x
Pascal Volk <neverseen@users.sourceforge.net>
parents:
102
diff
changeset
|
412 |
SELECT userid, passwd, smtp, pop3, imap, sieve |
437
9823548b2717
pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
382
diff
changeset
|
413 |
FROM users, service_set |
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
414 |
WHERE gid = (SELECT gid |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
415 |
FROM domain_name |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
416 |
WHERE domainname = the_domain) |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
417 |
AND local_part = localpart |
437
9823548b2717
pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
382
diff
changeset
|
418 |
AND service_set.ssid = users.ssid |
75
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
419 |
LOOP |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
420 |
RETURN NEXT record; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
421 |
END LOOP; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
422 |
RETURN; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
423 |
END; |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
424 |
$$ LANGUAGE plpgsql STABLE |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
425 |
RETURNS NULL ON NULL INPUT |
af813ede1e19
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
74
diff
changeset
|
426 |
EXTERNAL SECURITY INVOKER; |