127 passwd varchar(270) NOT NULL, |
127 passwd varchar(270) NOT NULL, |
128 name varchar(128) NULL, |
128 name varchar(128) NULL, |
129 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
129 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
130 gid bigint NOT NULL, |
130 gid bigint NOT NULL, |
131 mid bigint NOT NULL DEFAULT 1, |
131 mid bigint NOT NULL DEFAULT 1, |
132 qid bigint NULL DEFAULT NULL, |
132 qid bigint NOT NULL DEFAULT 1, |
133 ssid bigint NULL DEFAULT NULL, |
133 ssid bigint NOT NULL DEFAULT 1, |
134 tid bigint NULL DEFAULT NULL, |
134 tid bigint NOT NULL DEFAULT 1, |
135 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
135 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
136 CONSTRAINT ukey_users_uid UNIQUE (uid), |
136 CONSTRAINT ukey_users_uid UNIQUE (uid), |
137 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
137 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
138 REFERENCES domain_data (gid), |
138 REFERENCES domain_data (gid), |
139 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
139 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
344 record dovecotpassword; |
344 record dovecotpassword; |
345 userid varchar(320) := localpart || '@' || the_domain; |
345 userid varchar(320) := localpart || '@' || the_domain; |
346 BEGIN |
346 BEGIN |
347 FOR record IN |
347 FOR record IN |
348 SELECT userid, passwd, smtp, pop3, imap, managesieve |
348 SELECT userid, passwd, smtp, pop3, imap, managesieve |
349 FROM users, service_set, domain_data |
349 FROM users, service_set |
350 WHERE users.gid = (SELECT gid |
350 WHERE gid = (SELECT gid |
351 FROM domain_name |
351 FROM domain_name |
352 WHERE domainname = the_domain) |
352 WHERE domainname = the_domain) |
353 AND local_part = localpart |
353 AND local_part = localpart |
354 AND service_set.ssid = users.ssid |
354 AND service_set.ssid = users.ssid |
355 AND users.gid = domain_data.gid |
|
356 AND CASE WHEN |
|
357 users.ssid IS NOT NULL |
|
358 THEN |
|
359 service_set.ssid = users.ssid |
|
360 ELSE |
|
361 service_set.ssid = domain_data.ssid |
|
362 END |
|
363 LOOP |
355 LOOP |
364 RETURN NEXT record; |
356 RETURN NEXT record; |
365 END LOOP; |
357 END LOOP; |
366 RETURN; |
358 RETURN; |
367 END; |
359 END; |
394 WHERE users.gid = did |
386 WHERE users.gid = did |
395 AND users.local_part = localpart |
387 AND users.local_part = localpart |
396 AND maillocation.mid = users.mid |
388 AND maillocation.mid = users.mid |
397 AND mailboxformat.fid = maillocation.fid |
389 AND mailboxformat.fid = maillocation.fid |
398 AND domain_data.gid = did |
390 AND domain_data.gid = did |
399 AND CASE WHEN |
391 AND quotalimit.qid = users.qid |
400 users.qid IS NOT NULL |
|
401 THEN |
|
402 quotalimit.qid = users.qid |
|
403 ELSE |
|
404 quotalimit.qid = domain_data.qid |
|
405 END |
|
406 LOOP |
392 LOOP |
407 RETURN NEXT record; |
393 RETURN NEXT record; |
408 END LOOP; |
394 END LOOP; |
409 RETURN; |
395 RETURN; |
410 END; |
396 END; |
544 RETURNS SETOF recipient_transport |
530 RETURNS SETOF recipient_transport |
545 AS $$ |
531 AS $$ |
546 DECLARE |
532 DECLARE |
547 record recipient_transport; |
533 record recipient_transport; |
548 recipient varchar(320) := localpart || '@' || the_domain; |
534 recipient varchar(320) := localpart || '@' || the_domain; |
549 did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); |
535 BEGIN |
550 transport_id bigint := (SELECT tid FROM users |
|
551 WHERE gid = did AND local_part = localpart); |
|
552 BEGIN |
|
553 IF transport_id IS NULL THEN |
|
554 SELECT tid INTO STRICT transport_id |
|
555 FROM domain_data |
|
556 WHERE gid = did; |
|
557 END IF; |
|
558 |
|
559 FOR record IN |
536 FOR record IN |
560 SELECT recipient, transport |
537 SELECT recipient, transport |
561 FROM transport |
538 FROM transport |
562 WHERE tid = transport_id |
539 WHERE tid = (SELECT tid |
|
540 FROM users |
|
541 WHERE gid = (SELECT gid |
|
542 FROM domain_name |
|
543 WHERE domainname = the_domain) |
|
544 AND local_part = localpart) |
563 LOOP |
545 LOOP |
564 RETURN NEXT record; |
546 RETURN NEXT record; |
565 END LOOP; |
547 END LOOP; |
566 RETURN; |
548 RETURN; |
567 END; |
549 END; |