Fortran language simple table function

The code in this example creates a table function that takes a variable number of string representations of time, which is separated by commas (for example "12:30:55,4:20:18”). The function outputs one row for each time-representation; each row displays the time, split into its component parts.

program timeSplitManyProgram
call nzaeSetConnectionPointName('timeSplitManyRemote')
call nzaeRun()
stop
end
subroutine nzaeHandleRequest(handle)
integer hasNext, isNull, onCharacter, result
character(1) testChar
character(10) timeString
character(1000) timeStrings
hasNext = -1
isNull = 0
onCharacter = 0
timeString = '0'
timeStrings = '0'
c EXIT IF THERE ARE NO MORE INPUT ROWS.
10 call nzaeGetNext(handle, hasNext)
if (hasNext .eq. 0) then
goto 30
endif
c GET OUR INPUT.
call nzaeGetInputString(handle, 0, timeStrings, isNull)
c SET THE TIME-STRING.
20 timeString = timeStrings(onCharacter+1:onCharacter+9)
c OUTPUT ONE ROW.
call nzaeSetOutputString(handle, 0, timeString(1:2))
call nzaeSetOutputString(handle, 1, timeString(4:5))
call nzaeSetOutputString(handle, 2, timeString(7:8))
call nzaeOutputResult(handle)
c LOOP AS APPROPRAITE (IF WE FIND MORE COMMAS).
onCharacter = onCharacter + 9
if (timeStrings(onCharacter:onCharacter) .eq. ',') then
goto 20
endif
c LOOK FOR MORE INPUT ROWS.
goto 10
30 return
end

Use the standard compile:

$NZ_EXPORT_DIR/ae/utilities/bin/compile_ae --language fortran --version 3 \
--template compile timeSplitMany.f

Register the newly created "timeSplitMany" executable:

$NZ_EXPORT_DIR/ae/utilities/bin/register_ae --language fortran --version 3 \
--template udtf --exe timeSplitMany \
--sig "time_split_many(varchar(1000))" \
--return "table(hour varchar(2), minute varchar(2), second varchar(2))"

Run the query in nzsql:

SELECT * FROM TABLE WITH FINAL (time_split_many('13:22:47,22:12:45,03:22:09'));
HOUR | MINUTE | SECOND
------+--------+--------
13 | 22 | 47
22 | 12 | 45
03 | 22 | 09
(3 rows)