165 

   166 -- ---

   167 -- Drop all known v0.5 types (the dirty way)

   168 -- ---

   169 DROP TYPE address_maildir CASCADE;

   170 DROP TYPE dovecotpassword CASCADE;

   171 DROP TYPE dovecotuser CASCADE;

   172 DROP TYPE recipient_destination CASCADE;

   173 DROP TYPE recipient_transport CASCADE;

   174 DROP TYPE recipient_uid CASCADE;

   175 DROP TYPE sender_login CASCADE;

   176 

   177 -- ######################## TYPEs ########################################### --

   178 

   179 -- ---

   180 -- Data type for function postfix_virtual_mailbox(varchar, varchar)

   181 -- ---

   182 CREATE TYPE address_maildir AS (

   183     address varchar(320),

   184     maildir text

   185 );

   186 -- ---

   187 -- Data type for function dovecotpassword(varchar, varchar)

   188 -- ---

   189 CREATE TYPE dovecotpassword AS (

   190     userid      varchar(320),

   191     password    varchar(270),

   192     smtp        boolean,

   193     pop3        boolean,

   194     imap        boolean,

   195     managesieve boolean

   196 );

   197 -- ---

   198 -- Data type for function dovecotquotauser(varchar, varchar)

   199 -- ---

   200 CREATE TYPE dovecotquotauser AS (

   201     userid      varchar(320),

   202     uid         bigint,

   203     gid         bigint,

   204     home        text,

   205     mail        text,

   206     quota_rule  text

   207 );

   208 -- ---

   209 -- Data type for function dovecotuser(varchar, varchar)

   210 -- ---

   211 CREATE TYPE dovecotuser AS (

   212     userid      varchar(320),

   213     uid         bigint,

   214     gid         bigint,

   215     home        text,

   216     mail        text

   217 );

   218 -- ---

   219 -- Data type for functions: postfix_relocated_map(varchar, varchar)

   220 --                          postfix_virtual_alias_map(varchar, varchar)

   221 -- ---

   222 CREATE TYPE recipient_destination AS (

   223     recipient   varchar(320),

   224     destination text

   225 );

   226 -- ---

   227 -- Data type for function postfix_transport_map(varchar, varchar)

   228 -- ---

   229 CREATE TYPE recipient_transport AS (

   230     recipient   varchar(320),

   231     transport   text

   232 );

   233 -- ---

   234 -- Data type for function postfix_virtual_uid_map(varchar, varchar)

   235 -- ---

   236 CREATE TYPE recipient_uid AS (

   237     recipient   varchar(320),

   238     uid         bigint

   239 );

   240 -- ---

   241 -- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)

   242 -- ---

   243 CREATE TYPE sender_login AS (

   244     sender  varchar(320),

   245     login   text

   246 );

   247 

   248 -- ######################## FUNCTIONs ####################################### --

   249 

   250 -- ---

   251 -- Parameters (from login name [localpart@the_domain]):

   252 --      varchar localpart

   253 --      varchar the_domain

   254 -- Returns: dovecotpassword records

   255 -- ---

   256 CREATE OR REPLACE FUNCTION dovecotpassword(

   257     IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword

   258 AS $$

   259     DECLARE

   260         record dovecotpassword;

   261         userid varchar(320) := localpart || '@' || the_domain;

   262     BEGIN

   263         FOR record IN

   264             SELECT userid, passwd, smtp, pop3, imap, managesieve

   265               FROM users, service_set

   266              WHERE gid = (SELECT gid

   267                             FROM domain_name

   268                            WHERE domainname = the_domain)

   269                AND local_part = localpart

   270                AND service_set.ssid = users.ssid

   271             LOOP

   272                 RETURN NEXT record;

   273             END LOOP;

   274         RETURN;

   275     END;

   276 $$ LANGUAGE plpgsql STABLE

   277 RETURNS NULL ON NULL INPUT

   278 EXTERNAL SECURITY INVOKER;

   279 -- ---

   280 -- Nearly the same as function dovecotuser below. It returns additionally the

   281 -- field quota_rule.

   282 -- ---

   283 CREATE OR REPLACE FUNCTION dovecotquotauser(

   284     IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser

   285 AS $$

   286     DECLARE

   287         record dovecotquotauser;

   288         userid varchar(320) := localpart || '@' || the_domain;

   289         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   290     BEGIN

   291         FOR record IN

   292             SELECT userid, uid, did, domaindir || '/' || uid AS home,

   293                    format || ':~/' || directory AS mail, '*:bytes=' ||

   294                    bytes || ':messages=' || messages AS quota_rule

   295               FROM users, domain_data, mailboxformat, maillocation, quotalimit

   296              WHERE users.gid = did

   297                AND users.local_part = localpart

   298                AND maillocation.mid = users.mid

   299                AND mailboxformat.fid = maillocation.fid

   300                AND domain_data.gid = did

   301                AND quotalimit.qid = users.qid

   302             LOOP

   303                 RETURN NEXT record;

   304             END LOOP;

   305         RETURN;

   306     END;

   307 $$ LANGUAGE plpgsql STABLE

   308 RETURNS NULL ON NULL INPUT

   309 EXTERNAL SECURITY INVOKER;

   310 -- ---

   311 -- Parameters (from login name [localpart@the_domain]):

   312 --      varchar localpart

   313 --      varchar the_domain

   314 -- Returns: dovecotuser records

   315 -- ---

   316 CREATE OR REPLACE FUNCTION dovecotuser(

   317     IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser

   318 AS $$

   319     DECLARE

   320         record dovecotuser;

   321         userid varchar(320) := localpart || '@' || the_domain;

   322         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   323     BEGIN

   324         FOR record IN

   325             SELECT userid, uid, did, domaindir || '/' || uid AS home,

   326                    format || ':~/' || directory AS mail

   327               FROM users, domain_data, mailboxformat, maillocation

   328              WHERE users.gid = did

   329                AND users.local_part = localpart

   330                AND maillocation.mid = users.mid

   331                AND mailboxformat.fid = maillocation.fid

   332                AND domain_data.gid = did

   333             LOOP

   334                 RETURN NEXT record;

   335             END LOOP;

   336         RETURN;

   337     END;

   338 $$ LANGUAGE plpgsql STABLE

   339 RETURNS NULL ON NULL INPUT

   340 EXTERNAL SECURITY INVOKER;

   341 -- ---

   342 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):

   343 --      varchar localpart

   344 --      varchar the_domain

   345 -- Returns: recipient_destination records

   346 -- ---

   347 CREATE OR REPLACE FUNCTION postfix_relocated_map(

   348     IN localpart varchar, IN the_domain varchar)

   349     RETURNS SETOF recipient_destination

   350 AS $$

   351     DECLARE

   352         record recipient_destination;

   353         recipient varchar(320) := localpart || '@' || the_domain;

   354         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   355     BEGIN

   356         FOR record IN

   357             SELECT recipient, destination

   358               FROM relocated

   359              WHERE gid = did

   360                AND address = localpart

   361             LOOP

   362                 RETURN NEXT record;

   363             END LOOP;

   364         RETURN;

   365     END;

   366 $$ LANGUAGE plpgsql STABLE

   367 RETURNS NULL ON NULL INPUT

   368 EXTERNAL SECURITY INVOKER;

   369 -- ---

   370 -- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):

   371 --      varchar localpart

   372 --      varchar the_domain

   373 -- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):

   374 --      set of sender_login records.

   375 -- ---

   376 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(

   377     IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login

   378 AS $$

   379     DECLARE

   380         rec sender_login;

   381         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   382         sender varchar(320) := localpart || '@' || the_domain;

   383     BEGIN

   384         -- Get all addresses for 'localpart' in the primary and aliased domains

   385         FOR rec IN

   386             SELECT sender, local_part || '@' || domainname

   387               FROM domain_name, users

   388              WHERE domain_name.gid = did

   389                AND users.gid = did

   390                AND users.local_part = localpart

   391             LOOP

   392                 RETURN NEXT rec;

   393             END LOOP;

   394         IF NOT FOUND THEN

   395             -- Loop over the alias addresses for localpart@the_domain

   396             FOR rec IN

   397                 SELECT DISTINCT sender, destination

   398                   FROM alias

   399                  WHERE alias.gid = did

   400                    AND alias.address = localpart

   401                 LOOP

   402                     RETURN NEXT rec;

   403                 END LOOP;

   404         END IF;

   405         RETURN;

   406     END;

   407 $$ LANGUAGE plpgsql STABLE

   408 RETURNS NULL ON NULL INPUT

   409 EXTERNAL SECURITY INVOKER;

   410 -- ---

   411 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):

   412 --      varchar localpart

   413 --      varchar the_domain

   414 -- Returns: recipient_transport records

   415 -- ---

   416 CREATE OR REPLACE FUNCTION postfix_transport_map(

   417     IN localpart varchar, IN the_domain varchar)

   418     RETURNS SETOF recipient_transport

   419 AS $$

   420     DECLARE

   421         record recipient_transport;

   422         recipient varchar(320) := localpart || '@' || the_domain;

   423     BEGIN

   424         FOR record IN

   425             SELECT recipient, transport

   426               FROM transport

   427              WHERE tid = (SELECT tid

   428                             FROM users

   429                            WHERE gid = (SELECT gid

   430                                           FROM domain_name

   431                                          WHERE domainname = the_domain)

   432                              AND local_part = localpart)

   433             LOOP

   434                 RETURN NEXT record;

   435             END LOOP;

   436         RETURN;

   437     END;

   438 $$ LANGUAGE plpgsql STABLE

   439 RETURNS NULL ON NULL INPUT

   440 EXTERNAL SECURITY INVOKER;

   441 -- ---

   442 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):

   443 --      varchar localpart

   444 --      varchar the_domain

   445 -- Returns: recipient_destination records

   446 -- ---

   447 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(

   448     IN localpart varchar, IN the_domain varchar)

   449     RETURNS SETOF recipient_destination

   450 AS $$

   451     DECLARE

   452         record recipient_destination;

   453         recipient varchar(320) := localpart || '@' || the_domain;

   454         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   455     BEGIN

   456         FOR record IN

   457             SELECT recipient, destination

   458               FROM alias

   459              WHERE gid = did

   460                AND address = localpart

   461             LOOP

   462                 RETURN NEXT record;

   463             END LOOP;

   464         RETURN;

   465     END;

   466 $$ LANGUAGE plpgsql STABLE

   467 RETURNS NULL ON NULL INPUT

   468 EXTERNAL SECURITY INVOKER;

   469 -- ---

   470 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):

   471 --      varchar localpart

   472 --      varchar the_domain

   473 -- Returns: address_maildir records

   474 -- ---

   475 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(

   476    IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir

   477 AS $$

   478     DECLARE

   479         rec address_maildir;

   480         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   481         address varchar(320) := localpart || '@' || the_domain;

   482     BEGIN

   483         FOR rec IN

   484             SELECT address, domaindir||'/'||users.uid||'/'||directory||'/'

   485               FROM domain_data, users, maillocation

   486              WHERE domain_data.gid = did

   487                AND users.gid = did

   488                AND users.local_part = localpart

   489                AND maillocation.mid = users.mid

   490             LOOP

   491                 RETURN NEXT rec;

   492             END LOOP;

   493         RETURN;

   494     END;

   495 $$ LANGUAGE plpgsql STABLE

   496 RETURNS NULL ON NULL INPUT

   497 EXTERNAL SECURITY INVOKER;

   498 -- ---

   499 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):

   500 --      varchar localpart

   501 --      varchar the_domain

   502 -- Returns: recipient_uid records

   503 -- ---

   504 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(

   505     IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid

   506 AS $$

   507     DECLARE

   508         record recipient_uid;

   509         recipient varchar(320) := localpart || '@' || the_domain;

   510     BEGIN

   511         FOR record IN

   512             SELECT recipient, uid

   513               FROM users

   514              WHERE gid = (SELECT gid

   515                             FROM domain_name

   516                            WHERE domainname = the_domain)

   517                AND local_part = localpart

   518             LOOP

   519                 RETURN NEXT record;

   520             END LOOP;

   521         RETURN;

   522     END;

   523 $$ LANGUAGE plpgsql STABLE

   524 RETURNS NULL ON NULL INPUT

   525 EXTERNAL SECURITY INVOKER;