+CREATE FUNCTION update_song_data(
+ new_generation bigint
+ , OUT total_songs bigint
+ , OUT total_artists bigint
+ , OUT total_albums bigint
+ , OUT new_songs bigint
+ , OUT new_artists bigint
+ , OUT new_albums bigint)
+AS
+$$
+DECLARE
+ song_data RECORD;
+BEGIN
+ total_songs = 0;
+ new_songs = 0;
+ new_artists = 0;
+ new_albums = 0;
+
+ FOR song_data IN
+ SELECT path, artist, album
+ FROM tmp_songs
+ LOOP
+ total_songs = total_songs + 1;
+
+ UPDATE songs
+ SET generation = new_generation
+ WHERE path = song_data.path;
+
+ IF ( NOT FOUND ) THEN
+ new_songs = new_songs + 1;
+ INSERT INTO songs(path, artist, album, generation)
+ values(song_data.path, song_data.artist,
+ song_data.album, new_generation);
+ END IF;
+ -----------------------------
+ UPDATE artists
+ SET generation = new_generation
+ WHERE artist = song_data.artist;
+
+ IF ( NOT FOUND ) THEN
+ new_artists = new_artists + 1;
+ INSERT INTO artists(artist, generation)
+ VALUES(song_data.artist, new_generation);
+ END IF;
+ -----------------------------
+ UPDATE albums
+ SET generation = new_generation
+ WHERE album = song_data.album
+ AND artist = song_data.artist;
+
+ IF ( NOT FOUND ) THEN
+ new_albums = new_albums + 1;
+ INSERT INTO albums(album, artist, generation)
+ VALUES(song_data.album, song_data.artist, new_generation);
+ END IF;
+ END LOOP;
+
+ SELECT COUNT(*) INTO total_artists FROM artists WHERE generation = new_generation;
+ SELECT COUNT(*) INTO total_albums FROM albums WHERE generation = new_generation;
+
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+