PL/pgSQL Coupled Loops
I love a challenge. A loyal Oracle PL/SQL developer said PL/pgSQL couldn’t support coupled loops and user-defined lists. Part true and part false. It’s true PL/pgSQL couldn’t support user-defined lists because it supports arrays. It’s false because PL/pgSQL supports an ARRAY_APPEND function that lets you manage arrays like Java’s ArrayList class.
Anyway, without further ado. You only need to create one data type because PL/pgSQL supports natural array syntax, like Java, C#, and other languages and doesn’t adhere rigidly to the Information Definition Language (IDL) standard that Oracle imposes. Oracle requires creating an Attribute Data Type (ADT) for the string collections, which you can avoid in PL/pgSQL.
You do need to create a record structure type, like:
/* Create a lyric object type. */ CREATE TYPE lyric AS ( day VARCHAR(8) , gift VARCHAR(24)); |
You can build a function to accept an array of strings and an array of record structures that returns a new array constructed from parts of the two input arrays. The function also compares and matches the two arrays before returning an array that combines strings for a songs lyrics. While the example uses the ever boring 12 Days of Christmas, I’d love another for examples. It just needs to use this type of repetitive structure. If you have one that you would like to share let me know.
The twelve_days function is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | CREATE FUNCTION twelve_days ( IN pv_days VARCHAR(8)[] , IN pv_gifts LYRIC[] ) RETURNS VARCHAR[] AS $$ DECLARE /* Initialize the collection of lyrics. */ lv_retval VARCHAR(36)[114]; BEGIN /* Read forward through the days. */ FOR i IN 1..ARRAY_LENGTH(pv_days,1) LOOP lv_retval := ARRAY_APPEND(lv_retval,('On the ' || pv_days[i] || ' day of Christmas')::text); lv_retval := ARRAY_APPEND(lv_retval,('my true love sent to me:')::text); /* Read backward through the lyrics based on the ascending value of the day. */ FOR j IN REVERSE i..1 LOOP IF i = 1 THEN lv_retval := ARRAY_APPEND(lv_retval,('-'||'A'||' '|| pv_gifts[j].gift)::text); ELSIF j <= i THEN lv_retval := ARRAY_APPEND(lv_retval,('-'|| pv_gifts[j].day ||' '|| pv_gifts[j].gift )::text); END IF; END LOOP; /* A line break by verse. */ lv_retval := ARRAY_APPEND(lv_retval,' '::text); END LOOP; /* Return the song's lyrics. */ RETURN lv_retval; END; $$ LANGUAGE plpgsql; |
Then, you can test it with this query:
SELECT UNNEST(twelve_days(ARRAY['first','second','third','fourth' ,'fifth','sixth','seventh','eighth' ,'nineth','tenth','eleventh','twelfth'] ,ARRAY[('and a','Partridge in a pear tree')::lyric ,('Two','Turtle doves')::lyric ,('Three','French hens')::lyric ,('Four','Calling birds')::lyric ,('Five','Golden rings')::lyric ,('Six','Geese a laying')::lyric ,('Seven','Swans a swimming')::lyric ,('Eight','Maids a milking')::lyric ,('Nine','Ladies dancing')::lyric ,('Ten','Lords a leaping')::lyric ,('Eleven','Pipers piping')::lyric ,('Twelve','Drummers drumming')::lyric])) AS "12-Days of Christmas"; |
It prints:
12-Days of Christmas ---------------------------------- On the first day of Christmas my true love sent to me: -A Partridge in a pear tree On the second day of Christmas my true love sent to me: -Two Turtle doves -and a Partridge in a pear tree On the third day of Christmas my true love sent to me: -Three French hens -Two Turtle doves -and a Partridge in a pear tree ... Redacted for space ... On the twelfth day of Christmas my true love sent to me: -Twelve Drummers drumming -Eleven Pipers piping -Ten Lords a leaping -Nine Ladies dancing -Eight Maids a milking -Seven Swans a swimming -Six Geese a laying -Five Golden rings -Four Calling birds -Three French hens -Two Turtle doves -and a Partridge in a pear tree |
So, I believe that I met the challenge and hopefully provided a concrete example of some syntax that seems to be missing from most of the typical places.
Subscribe via RSS