CREATE LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS udf_forwarders_out(TEXT,TEXT,CHAR);

CREATE FUNCTION udf_forwarders_out( email_str TEXT,
                                    vacation_domain TEXT ,
                                    list_seperator CHAR 
                                  )
   RETURNS TEXT AS $$
   DECLARE
      forward_str text;
      local_email_part TEXT;
      domain_email_part TEXT;
   BEGIN
      
      -- get list of forwarders
      --
      SELECT goto INTO forward_str FROM alias WHERE address=email_str;

      -- entferne mailbox emailadresse
      --
      forward_str = replace(forward_str, email_str, '' );

      -- entferne vacation adresse
      --
      local_email_part = substring(email_str, 1, position('@' in email_str) - 1);
      domain_email_part = substring(email_str,  position('@' in email_str) + 1 );
      forward_str = replace(forward_str, local_email_part || '#' || domain_email_part || '@' || vacation_domain, '');

      -- enferne doppelte seperatorzeichen
      --
      WHILE position( list_seperator || list_seperator in forward_str ) > 0 LOOP
         forward_str = replace(forward_str, list_seperator || list_seperator , '');
      END LOOP;

      -- entferne erstes zeichen wenn es das seperatorzeichen ist
      --
      IF substring(forward_str,1,1)  = list_seperator THEN
         forward_str = substring(forward_str from 2);
      END IF;


      -- entferne letztes zeichen wenn es das seperatorzeichen ist
      --
      IF substring(forward_str from  char_length(forward_str))  = list_seperator THEN
         forward_str = substring(forward_str, 1, char_length(forward_str) - 1);
      END IF;


      -- forward_str = substring(forward_str from  char_length(forward_str));

      RETURN forward_str;
   END;
   $$ LANGUAGE plpgsql;


SELECT udf_forwarders_out('ckubu@oopen.de','autoreply.oopen.de',',');