Question 90683 in PostgreSQL Question posted in
The official documentation can be found here.

Use of auxiliary tables in Postgresql if temporary tables don't work

I’m trying to implement properly a trigger function in Postgresql. The database consists of three tables, two of which are connected by a third one which is an intermediary:

Table MUSICIAN (PK = id_musician)

id_musician name birth death gender nationality num_awards
1 John Lennon 1940-10-09 1980-12-08 M British 0
2 Paul McCartney 1942-06-18 NULL M British 0
3 Joep Beving 1976-01-09 NULL M Dutch 0
4 Amy Winehouse 1983-09-14 2011-07-23 F British 0
5 Wim Mertens 1953-05-14 NULL M Belgian 0

TABLE COMPOSER (PK = FK = id_musician, id_song)

id_musician id_song
1 1
2 1
3 2
4 3
4 4
5 5

TABLE SONG (PK = id_song; FK = id_album)

id_song title duration id_album awards
1 A Hard Day’s Night 00:02:34 1 1
2 Henosis 00:06:44 2 1
3 Rehab 00:03:34 3 6
4 Back To Black 00:04:01 3 2
5 Close Cover 00:03:31 4 0

The trigger function is implemented on the table SONG for calculating the column num_awards in the table MUSICIAN and the results expected are as follows:

id_musician name birth death gender nationality num_awards
1 John Lennon 1940-10-09 1980-12-08 M British 1
2 Paul McCartney 1942-06-18 NULL M British 1
3 Joep Beving 1976-01-09 NULL M Dutch 1
4 Amy Winehouse 1983-09-14 2011-07-23 F British 8
5 Wim Mertens 1953-05-14 NULL M Belgian 0

As you can see, the column num_awards sums the awards from all the songs present in the table SONG for each composer. For that purpose I’ve coded the following trigger function:

CREATE OR REPLACE FUNCTION update_num_awards()
RETURNS trigger AS $$ 
BEGIN
    CREATE TABLE IF NOT EXISTS temp1 AS TABLE COMPOSER;
    CREATE TABLE IF NOT EXISTS temp2 AS TABLE SONG;
    IF (TG_OP = 'INSERT') THEN 
        UPDATE MUSICIAN
        SET num_awards = num_awards + NEW.awards
        WHERE id_musician IN (SELECT c.id_musician
                            FROM COMPOSER AS c JOIN SONG
                            ON(c.id_song = NEW.id_song));
    ELSIF (TG_OP = 'UPDATE') THEN
        UPDATE MUSICIAN
        SET num_awards = num_awards + NEW.awards
        WHERE id_musician IN (SELECT c.id_musician
                            FROM COMPOSER AS c JOIN SONG
                            ON(c.id_song = NEW.id_song));
        UPDATE MUSICIAN
        SET num_awards = num_awards - OLD.awards
        WHERE id_musician IN (SELECT c.id_musician
                            FROM temp1 AS t JOIN temp2
                            ON(c.id_song = OLD.id_song));   
    ELSIF (TG_OP = 'DELETE') THEN
        UPDATE MUSICIAN
        SET num_awards = num_awards - OLD.awards
        WHERE id_musician IN (SELECT c.id_musician
                            FROM temp1 AS t JOIN temp2
                            ON(c.id_song = OLD.id_song));
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trigger_update_num_awards AFTER INSERT OR DELETE OR UPDATE ON SONG 
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE update_num_awards();

The function is triggered AFTER insert, delete or update in the table SONG. When inserting, coding seems pretty straightforward but when it comes to deleting things start to get difficult – the subquery I’ve coded doesn’t work because rows may have already disappeared from the COMPOSER and SONG tables. I’ve tried to use temporary tables but they don’t seem to work – somehow they vanish before the delete operation begins. So the only solution I’ve got through with is creating two permanent auxiliary tables, temp1 and temp2, to make a copy of COMPOSER and SONG with before each delete operation.

And my question is, how could temporary tables be performed in this case? Personally, despite my piece of code working correctly for what I want, I would like to improve it or to make it more simple and elegant.

Any help would be greatly appreciated.

Regards

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    This answer consists of an update function and a trigger which perform on the table COMPOSER.

    It's simpler to implement, covers more cases and no auxiliary tables or deferrability are necessary.

    CREATE OR REPLACE FUNCTION update_num_awards()
RETURNS trigger AS $$ 
BEGIN 
    IF (TG_OP = 'INSERT') THEN 
        UPDATE MUSICIAN
        SET num_awards = num_awards + (SELECT s.awards
                                       FROM SONG AS s
                                       WHERE s.id_song IN
                                       (SELECT s.id_song
                                        FROM SONG AS s JOIN COMPOSER
                                        ON(s.id_song = NEW.id_song)))
        WHERE id_musician = NEW.id_musician;
    ELSIF (TG_OP = 'UPDATE') THEN
        UPDATE MUSICIAN
        SET num_awards = num_awards + (SELECT s.awards
                                       FROM SONG AS s
                                       WHERE s.id_song IN
                                       (SELECT s.id_song
                                        FROM SONG AS s JOIN COMPOSER
                                        ON(s.id_song = NEW.id_song)))
        WHERE id_musician = NEW.id_musician;
        UPDATE MUSICIAN
        SET num_awards = num_awards - (SELECT s.awards
                                       FROM SONG AS s
                                       WHERE s.id_song IN
                                       (SELECT s.id_song
                                        FROM SONG AS s JOIN COMPOSER
                                        ON(s.id_song = OLD.id_song)))
        WHERE id_musician = OLD.id_musician;    
    ELSIF (TG_OP = 'DELETE') THEN
        UPDATE MUSICIAN
        SET num_awards = num_awards - (SELECT s.awards
                                       FROM SONG AS s
                                       WHERE s.id_song IN
                                       (SELECT s.id_song
                                        FROM SONG AS s JOIN COMPOSER
                                        ON(s.id_song = OLD.id_song)))
        WHERE id_musician = OLD.id_musician;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_num_awards AFTER INSERT OR DELETE OR UPDATE OF
id_musician, id_song ON COMPOSER
FOR EACH ROW EXECUTE PROCEDURE update_num_awards();

  2. 0

    It seems like doing this on song is doing it the hard way. It’s composer that has most of the effects. Delete and insert are easy. Update can be treated as a delete + insert.

    • Composer
      • on delete: musician.num_awards = musician.num_awards – song.awards
      • on insert: musician.num_awards = musician.num_awards + song.awards
      • on update
        • old.musician.num_awards = old.musician.num_awards – old.song.awards
        • new.musician.num_awards = new.musician.num_awards + new.song.awards

    When a song is inserted, it doesn’t matter because it is not associated with a musician. When a song is deleted, it will cascade to delete the associated composers (assuming you’ve set up cascade deletes). That leaves one trigger, when a song is updated.

    • Song
      • on update: for all musicians who composed the song, musician.num_awards = musician.num_awards – old.awards + new.awards

    No temp tables should be necessary.

    Login or Signup to reply.
  3. 0

    Following @Schwern’s suggestions:

    /*Function and trigger for the COMPOSER table*/

CREATE OR REPLACE FUNCTION update_composer()
RETURNS trigger AS $$ 
BEGIN 
    IF (TG_OP = 'INSERT') THEN 
        UPDATE MUSICIAN
        SET num_awards = num_awards + (SELECT s.awards
                                       FROM SONG AS s
                                       WHERE s.id_song IN
                                       (SELECT s.id_song
                                        FROM SONG AS s JOIN COMPOSER
                                        ON(s.id_song = NEW.id_song)))
        WHERE id_musician = NEW.id_musician;
    ELSIF (TG_OP = 'UPDATE') THEN
        UPDATE MUSICIAN
        SET num_awards = num_awards + (SELECT s.awards
                                       FROM SONG AS s
                                       WHERE s.id_song IN
                                       (SELECT s.id_song
                                        FROM SONG AS s JOIN COMPOSER
                                        ON(s.id_song = NEW.id_song)))
        WHERE id_musician = NEW.id_musician;
        UPDATE MUSICIAN
        SET num_awards = num_awards - (SELECT s.awards
                                       FROM SONG AS s
                                       WHERE s.id_song IN
                                       (SELECT s.id_song
                                        FROM SONG AS s JOIN COMPOSER
                                        ON(s.id_song = OLD.id_song)))
        WHERE id_musician = OLD.id_musician;    
    ELSIF (TG_OP = 'DELETE') THEN
        UPDATE MUSICIAN
        SET num_awards = num_awards - (SELECT s.awards
                                       FROM SONG AS s
                                       WHERE s.id_song IN
                                       (SELECT s.id_song
                                        FROM SONG AS s JOIN COMPOSER
                                        ON(s.id_song = OLD.id_song)))
        WHERE id_musician = OLD.id_musician;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_composer AFTER INSERT OR DELETE OR UPDATE OF
id_musician, id_song ON COMPOSER
FOR EACH ROW EXECUTE PROCEDURE update_composer();

/*Function and trigger for the SONG table*/

CREATE OR REPLACE FUNCTION update_song()
RETURNS trigger AS $$ 
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        UPDATE MUSICIAN
        SET num_awards = num_awards + NEW.awards
        WHERE id_musician IN (SELECT c.id_musician
                            FROM COMPOSER AS c JOIN SONG
                            ON(c.id_song = NEW.id_song));
        UPDATE MUSICIAN
        SET num_awards = num_awards - OLD.awards
        WHERE id_musician IN (SELECT c.id_musician
                            FROM COMPOSER AS c JOIN SONG
                            ON(c.id_song = OLD.id_song));   
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trigger_update_song AFTER UPDATE OF awards ON SONG 
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE update_song();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search