======Copy SMTP Rules - Database Query======

This is an advanced Database action. There is no simple query that will perform the required operation, so we have to create a custom PostgreSQL function to copy the data.

At a command prompt in the VPOP3 directory run

  psql
  
(The password is 'vpop3pass')

Then, type (or copy/paste) the text below, to create the required function

  CREATE OR REPLACE FUNCTION copysmtprules(fromid integer,toid integer) RETURNS integer AS $$
  DECLARE
    rl RECORD;
    newruleid INTEGER;
    cnt INTEGER := 0;
  BEGIN
    FOR rl IN SELECT * FROM rules.smtprules WHERE service=fromid ORDER BY ruleid LOOP
      INSERT INTO rules.smtprules (service,rulephase,ruleorder,name,orrule,action,data) VALUES(toid,rl.rulephase,rl.ruleorder,rl.name,rl.orrule,rl.action,rl.data) returning ruleid into newruleid;
      INSERT INTO rules.smtpruleconditions (ruleid,invert,field,comparison,data) SELECT newruleid, invert,field,comparison,data FROM rules.smtpruleconditions WHERE ruleid=rl.ruleid;
      cnt := cnt + 1;
    END LOOP;
    RETURN cnt;
  END;
  $$ LANGUAGE plpgsql;
  
  
Now, type:

  SELECT * FROM settings.settings WHERE name ILIKE '\\service-%\\~Name';
  
This will give you a list of all the Services created in VPOP3, and their IDs (the number after the 'service-' text). Take a note of the service ID you want to copy the rules from, and the service ID you want to copy the rules to.

Now, type:

  SELECT copysmtprules(<fromid>, <toid>);
  
For instance:
  SELECT copysmtprules(1, 20);
  
This will perform the copy operation and display the number of rules copied.

You do not need to stop VPOP3 while you do this, or restart VPOP3 afterwards.