Stored Procedure vs Stored Function

[rdonnay]

I have started looking at the usage of stored procedures to emulate the function of an index tag with a UDF.

For example, one of the index tags on this project uses the following index expression:
INDEX ON normalizeTicket(Ticket_No)+str(RecNo()) TAG TicketNo TO CHARGES.CDX

ISAM Smart Order feature of PGDBE seemed to handle this just fine, however there appears to be major performance issues.
Therefore, it may be necessary to use a SQL statement that uses a stored procedure call in the ORDER BY clause.
The PostGreSql documentation states that stored procedures to not return a value, so I don't know how this would work.
The documentation states that stored functions are available. Is this the proper strategy?

Code: Select all

FUNCTION normalizeTicket(xTic)

RETURN upper(left(strip(xTic)+space(10),10))

FUNCTION Strip( in_str, cExtra )
// syntax  strip(<c>)  returns uppercase void of spaces and punctuation
LOCAL x,y,z, xJunk:='- .,/;\|()&'
IF empty(in_str)
  RETURN("")
ENDIF
IF valtype(cExtra) == "C" .AND. !empty(cExtra)
  xJunk += cExtra
ENDIF
in_str := alltrim(in_str)
z := ''
x:=len(in_str)
FOR y := 1 TO x
//  z += IIF(substr(in_str,y,1)$'- .,/;\|()&',"",substr(in_str,y,1))  // strip out the junk
  z += IIF(in_str[y]$xJunk,"",in_str[y])    // strip out the junk
NEXT
RETURN(upper(z))

[k-insis]

Yes, you need to use FUNCTION for this

Procedures in postgresql are invoked within CALL PROCEDURE, where FUNCTION is within SELECT statement.
Procedure cannot return result set, while functions can (multiple too). So procedure is kinda like .exe called from RUN vs regular functions.

Also you need posgresql >= R11 to do both.

So you are imho correct to write strip() as FUNCTION .

I would use regexp_replace() in it to clean out/manipulate strings fast.
https://www.postgresql.org/docs/current ... ching.html

I have started looking at the usage of stored procedures to emulate the function of an index tag with a UDF.

For example, one of the index tags on this project uses the following index expression:
INDEX ON normalizeTicket(Ticket_No)+str(RecNo()) TAG TicketNo TO CHARGES.CDX

ISAM Smart Order feature of PGDBE seemed to handle this just fine, however there appears to be major performance issues.
Therefore, it may be necessary to use a SQL statement that uses a stored procedure call in the ORDER BY clause.
The PostGreSql documentation states that stored procedures to not return a value, so I don't know how this would work.
The documentation states that stored functions are available. Is this the proper strategy?

Code: Select all

FUNCTION normalizeTicket(xTic)

RETURN upper(left(strip(xTic)+space(10),10))

FUNCTION Strip( in_str, cExtra )
// syntax  strip(<c>)  returns uppercase void of spaces and punctuation
LOCAL x,y,z, xJunk:='- .,/;\|()&'
IF empty(in_str)
  RETURN("")
ENDIF
IF valtype(cExtra) == "C" .AND. !empty(cExtra)
  xJunk += cExtra
ENDIF
in_str := alltrim(in_str)
z := ''
x:=len(in_str)
FOR y := 1 TO x
//  z += IIF(substr(in_str,y,1)$'- .,/;\|()&',"",substr(in_str,y,1))  // strip out the junk
  z += IIF(in_str[y]$xJunk,"",in_str[y])    // strip out the junk
NEXT
RETURN(upper(z))