Ticket #647: Proc.sql

CREATE OR REPLACE FUNCTION sortticketchange() RETURNS int4 AS
$BODY$
DECLARE
        ticket_row ticket%ROWTYPE;
        ticket_change_row ticket_change%ROWTYPE;
        counter int4;
        time_row ticket_change%ROWTYPE;
        hascomment int4;
        authortext varchar;
BEGIN
        FOR ticket_row IN SELECT * FROM ticket WHERE id > 1 LOOP

                counter = 1;
                        
                FOR time_row IN SELECT DISTINCT(time) AS time FROM ticket_change WHERE ticket = ticket_row.id LOOP

                        RAISE NOTICE '%', time_row.ticket;

                        SELECT INTO hascomment COUNT(*) FROM ticket_change WHERE ticket = ticket_row.id AND ticket_change.time = time_row.ticket AND ticket_change.field = 'comment';

                        IF hascomment = 1 THEN  
                                
                                UPDATE ticket_change SET oldvalue = counter WHERE ticket = ticket_row.id AND time = time_row.ticket;
                                RAISE NOTICE 'UPDATING %', ticket_row.id;

                        ELSE

                                SELECT INTO authortext MIN(author) FROM ticket_change WHERE ticket = ticket_row.id AND time = time_row.ticket;

                                INSERT INTO ticket_change(ticket, time, author, field, oldvalue)
                                VALUES (ticket_row.id, time_row.ticket, authortext, 'comment', counter);
                                RAISE NOTICE 'INSERTING %', ticket_row.id;

                        END IF;                 

                        counter = counter + 1;

                END LOOP;

        END LOOP;

        RETURN 1;

END;
$BODY$
LANGUAGE PLPGSQL;

SELECT * FROM sortticketchange();