author | Pascal Volk <neverseen@users.sourceforge.net> |
Mon, 01 Sep 2008 03:11:23 +0000 | |
changeset 72 | ee0a0b5a8c2b |
child 73 | 11da3d9298b4 |
permissions | -rw-r--r-- |
72
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
1 |
-- $Id$ |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
2 |
|
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
3 |
-- --- Information: |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
4 |
-- 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
|
5 |
-- 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
|
6 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
7 |
|
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 |
-- Data type for function postfix_smtpd_sender_login(varchar, varchar) |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
10 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
11 |
CREATE TYPE sender_login AS ( |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
12 |
sender varchar(320), |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
13 |
login text |
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 |
|
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
16 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
17 |
-- 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
|
18 |
-- varchar localpart |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
19 |
-- varchar the_domain |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
20 |
-- 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
|
21 |
-- set of sender_login records. |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
22 |
-- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
23 |
-- Required access privileges for your postfix database user: |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
24 |
-- 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
|
25 |
-- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
26 |
-- 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
|
27 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
28 |
CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login( |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
29 |
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
|
30 |
AS $$ |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
31 |
DECLARE |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
32 |
rec sender_login; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
33 |
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
|
34 |
sender varchar(320) := localpart || '@' || the_domain; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
35 |
BEGIN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
36 |
-- 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
|
37 |
FOR rec IN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
38 |
SELECT sender, local_part || '@' || domainname |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
39 |
FROM domain_name, users |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
40 |
WHERE domain_name.gid = did |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
41 |
AND users.local_part = localpart |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
42 |
LOOP |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
43 |
RETURN NEXT rec; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
44 |
END LOOP; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
45 |
IF NOT FOUND THEN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
46 |
-- 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
|
47 |
FOR rec IN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
48 |
SELECT DISTINCT sender, destination |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
49 |
FROM alias |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
50 |
LEFT JOIN domain_name USING (gid) |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
51 |
WHERE alias.address = localpart |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
52 |
LOOP |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
53 |
RETURN NEXT rec; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
54 |
END LOOP; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
55 |
END IF; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
56 |
RETURN; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
57 |
END; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
58 |
$$ LANGUAGE plpgsql STABLE |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
59 |
RETURNS NULL ON NULL INPUT |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
60 |
EXTERNAL SECURITY INVOKER; |
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 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
65 |
-- 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
|
66 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
67 |
CREATE TYPE address_maildir AS ( |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
68 |
address varchar(320), |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
69 |
maildir text |
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 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
73 |
-- 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
|
74 |
-- varchar localpart |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
75 |
-- varchar the_domain |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
76 |
-- Returns: address_maildir records |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
77 |
-- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
78 |
-- Required access privileges for your postfix database user: |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
79 |
-- 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
|
80 |
-- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
81 |
-- 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
|
82 |
-- --- |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
83 |
CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
84 |
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
|
85 |
AS $$ |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
86 |
DECLARE |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
87 |
rec address_maildir; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
88 |
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
|
89 |
address varchar(320) := localpart || '@' || the_domain; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
90 |
BEGIN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
91 |
FOR rec IN |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
92 |
SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/' |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
93 |
FROM domain_data, users, maillocation |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
94 |
WHERE domain_data.gid = did |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
95 |
AND users.gid = did |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
96 |
AND users.local_part = localpart |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
97 |
AND maillocation.mid = users.mid |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
98 |
LOOP |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
99 |
RETURN NEXT rec; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
100 |
END LOOP; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
101 |
RETURN; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
102 |
END; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
103 |
$$ LANGUAGE plpgsql STABLE |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
104 |
RETURNS NULL ON NULL INPUT |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
105 |
EXTERNAL SECURITY INVOKER; |
ee0a0b5a8c2b
* 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff
changeset
|
106 |