How to create a stored procedure in POSTGRESQL that makes a FOR loop?-CodePudding

I am studying how to create triggers and stored procedures in POSTGRESQL and I have the task to update a column called num_long_title_songs of table ALBUM.

It is needed to count the number of songs with more than 12 characters on a table called songs and then update the value of the column in ALBUM.

I have created the next querie with a for loop, that does exactly what I want:

do $$
begin
    FOR i IN 1..100 LOOP
    UPDATE ALBUM
    SET  num_long_title_songs =(SELECT count(s.title)
                                FROM ALBUM a,SONG s
                                WHERE a.id_album=s.id_album
                                AND LENGTH(s.title) > 12
                                AND a.id_album=i
                                GROUP BY a.id_album
                                ORDER BY a.id_album)
    WHERE id_album IN (SELECT a.id_album
                                FROM ALBUM a,SONG s
                                WHERE a.id_album=s.id_album
                                AND LENGTH(s.title) > 12
                                AND a.id_album=i
                                GROUP BY a.id_album
                                ORDER BY a.id_album);
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
    END LOOP;
END; $$

The problem is that i need to create a stored procedure in order to make a trigger. I tried puting this inside the CREATE FUNCTION structure but I cant create it... could you help me in getting to know how to put this 'for' loop inside a function?

CodePudding user response：

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.