sql – How to create a stored procedure in POSTGRESQL that makes a FOR loop?

sql – How to create a stored procedure in POSTGRESQL that makes a FOR loop?

If you want to update the existing rows in table ALBUM, you just have to run the following UPDATE statement :

UPDATE ALBUM a
   SET num_long_title_songs = s.total
FROM ( SELECT s.id_album
            , count(s.title) AS total
         FROM SONG s
        WHERE LENGTH(s.title) > 12
        GROUP BY s.id_album
     ) AS s
WHERE a.id_album=s.id_album
  AND a.num_long_title_songs <> s.total ;

If you want to automatically update the num_long_title_songs field of table ALBUM when a row is inserted or updated in table SONG, then you need a trigger function :

CREATE OR REPLACE FUNCTION update_num_long_title_songs ()
RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
    UPDATE ALBUM a
       SET num_long_title_songs = s.total
    FROM ( SELECT s.id_album
                , count(s.title) AS total
             FROM SONG s
            WHERE LENGTH(s.title) > 12
              AND s.id_album = NEW.id_album
         ) AS s
    WHERE a.id_album=s.id_album
      AND a.num_long_title_songs <> s.total ;
END ;
$$ ;
    
CREATE TRIGGER after_insert_update_SONG AFTER INSERT OR UPDATE OF title ON SONG
FOR EACH ROW EXECUTE FUNCTION update_num_long_title_songs () ;

AFTER is important so that to take into account the new row inserted or updated in table SONG for the new value of the num_long_title_songs.

sql – How to create a stored procedure in POSTGRESQL that makes a FOR loop?