SQL TRANSACTION TO MOVE SIGNATURES TO SIGNATURE TABLE

Move_signatures.sql

BEGIN TRANSACTION;
  INSERT INTO signatures (document_id, signature_pkcs7, signature_data, signature_role)
  (
    SELECT document_id, document_owner_pkcs7 as sign, document_sign_from_json as json, 'owner' as role FROM documents where document_owner_pkcs7 IS NOT NULL
      UNION ALL
    SELECT document_id, document_contragent_pkcs7 as sign,  document_sign_from_json as json, 'contragent' as role FROM documents  where document_contragent_pkcs7 IS NOT NULL
      UNION ALL
    SELECT document_id, document_confidant_pkcs7 as sign, document_confidant_sign_json as json, 'confidant' as role FROM documents  where document_confidant_pkcs7 IS NOT NULL
  );

  UPDATE documents
  SET document_owner_pkcs7 = signature_id
  FROM signatures
  WHERE document_owner_pkcs7 = signature_pkcs7
    AND signature_role = 'owner'
    AND document_owner_pkcs7 NOTNULL
    AND document_owner_pkcs7 <> '';

  UPDATE documents
  SET document_contragent_pkcs7 = signature_id
  FROM signatures
  WHERE document_contragent_pkcs7 = signature_pkcs7
    AND signature_role = 'contragent'
    AND document_contragent_pkcs7 NOTNULL
    AND document_contragent_pkcs7 <> '';

  UPDATE documents
  SET document_confidant_pkcs7 = signature_id
  FROM signatures
  WHERE document_confidant_pkcs7 = signature_pkcs7
    AND signature_role = 'confidant'
    AND document_confidant_pkcs7 NOTNULL
    AND document_confidant_pkcs7 <> '';

  DELETE FROM signatures WHERE signature_pkcs7 ISNULL OR signature_pkcs7 = '';
COMMIT;