In first part Guide to Writing PLPGSQL functions, we covered the plpgsql function anatomy and basic IF and FOR loops. In this second part of our PLPGSQL Quick Guide series, we shall delve more into control flow. As we mentioned in the previous part, the following control flow constructs exist for PLPGSQL.
- FOR somevariable IN (1 ...someendnumber) LOOP .. END LOOP;
- FOR somevariable IN REVERSE someendnumber .. 1 BY somestep LOOP .. END LOOP;
- FOR somevariable IN EXECUTE(somesqlquery) LOOP ..RETURN NEXT; .. END LOOP;
- LOOP ..logic statements EXIT .. EXIT WHEN .. CONTINUE WHEN .. END LOOP;
- WHILE ... LOOP ... END LOOP;
- EXCEPTION WHEN .... WHEN ..
- Introduced in 8.3 RETURN QUERY which can be in any LOOP like structure or stand alone. This is
covered in New Features of PostgreSQL Functions
In this section we shall demonstrate looping thru sets of records and writing a set returning function. In the next section after,
we shall delve a little into recursive functions, doing table updates, and raising notices.