-- ---

-- Clean out the old stuff

-- ---

DROP TYPE dovecotpassword CASCADE;

-- ---

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

-- ---

CREATE TYPE dovecotpassword AS (

    userid    varchar(320),

    password  varchar(74),

    smtp      boolean,

    pop3      boolean,

    imap      boolean,

    sieve     boolean

);

-- ---

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

--      varchar localpart

--      varchar the_domain

-- Returns: dovecotpassword records

--

-- Required access privileges for your dovecot database user:

--      GRANT SELECT ON users, domain_name TO dovecot;

--

-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL

-- ---

CREATE OR REPLACE FUNCTION dovecotpassword(

    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword

AS $$

    DECLARE

        record dovecotpassword;

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

    BEGIN

        FOR record IN

            SELECT userid, passwd, smtp, pop3, imap, sieve

              FROM users

             WHERE gid = (SELECT gid

                            FROM domain_name

                           WHERE domainname = the_domain)

               AND local_part = localpart

            LOOP

                RETURN NEXT record;

            END LOOP;

        RETURN;

    END;

$$ LANGUAGE plpgsql STABLE

RETURNS NULL ON NULL INPUT

EXTERNAL SECURITY INVOKER;