/lib/sql.py - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 3015

sql.py: mk_insert_select(): INSERT IGNORE: Added duplicate key handling by using EXCEPTION block to catch unique_violation

+    ;
     BEGIN
         OPEN cur;
         /* Use extra loop inside EXCEPTION because the EXCEPTION block is expensive:
         "A block containing an EXCEPTION clause is significantly more expensive to
         enter and exit than a block without one."
         (http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html\
     #PLPGSQL-ERROR-TRAPPING)
         */
         <<outer>>
         WHILE true LOOP
             FETCH FROM cur INTO '''+(', '.join((c.to_str(db) for c in row)))+''';
             EXIT WHEN NOT FOUND;
             BEGIN
                 WHILE true LOOP
                     FETCH FROM cur INTO \
     '''+(', '.join((c.to_str(db) for c in row)))+''';
                     EXIT outer WHEN NOT FOUND;
     '''+mk_insert(sql_gen.Values(row).to_str(db))+'''
+    ;
                 END LOOP;
             EXCEPTION
                 WHEN unique_violation THEN NULL; -- outer loop continues to next row
             END;
         END LOOP;
         CLOSE cur;
     END;\
     '''

Also available in: Unified diff