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))

[rdonnay]

I wrote my first PostGreSql stored function which will replace an Xbase++ UDF which is used in a DBFCDX index.

I am struggling trying to learn the PLPGSQL language and how to use PgAdmin 4 to test the function.
Does anyone have experience with this?

This is the Xbase++ code:

Code: Select all

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))

This is the plpgsql code:

Code: Select all

CREATE OR REPLACE FUNCTION Strip( in_str CHAR, cExtra CHAR default '') RETURNS CHAR
language plpgsql
as $$
DECLARE
  i int;
  x int = Len(in_str);
  z char = '';
  xJunk char = '- .,/;\|()&' ;
BEGIN 
IF Empty(in_str)THEN
  RETURN '' ;
END IF;

in_str = alltrim(in_str) ;

FOR i IN 1..x LOOP
  IF Position(in_str[i] IN xJunk) > 0 THEN
  ELSE 
    z = z + in_str[y]	;
  END IF;
END LOOP;

RETURN upper(z);
END;
$$

[SlavkoDam]

You can do that with the SELECT command: SELECT Strip(p1,p2) AS cResult.

[Tom]

Just ask ChatGPT to help you. It knows PostGres and even Xbase++.

We don't use Stored Stuff with the PGDBE. That would be too much for our support team.

[rdonnay]

You can do that with the SELECT command: SELECT Strip(p1,p2) AS cResult.

Yes, I am familiar with how to use a stored function in a SELECT statement.
I am just not familiar with the PLPGSQL language structure.
The PgAdmin 4 debugger doesn't help me much to figure out what is wrong with my code.

[rdonnay]

Just ask ChatGPT to help you. It knows PostGres and even Xbase++.

Really? That is surprising that it would know Xbase++.
I must be living in the woods.
I will give it a try.
Thanks for that suggestion.

BTW - This PostGreSql project has stimulated me to want to continue in my career as a programmer and consultant.
I was really starting to get bored because I was running out of challenges consulting to my existing eXpress++ customers.
Also, politicians are boring and elections are boring. I can't wait for this election to be finished and the civil war to start .

[Tom]

That is surprising that it would know Xbase++.

It knows Xbase++ not as good as it knows Javascript, for instance, but it knows Xbase++ (and Clipper). The code created is simply not as reliable as the code for Javascript.

Just copy your function and ask ChatGPT to turn this Xbase++-code into a PG stored function.

[rdonnay]

My conversation with ChatGPT (before this, I was a non-believer. I think I have been reborn.) :

I want to convert an Xbase++ function to PLPGSQL.
ChatGPT said:
ChatGPT
I can help with that! Please provide the Xbase++ function you want to convert, and I'll assist you in translating it to PL/pgSQL.

You said:
FUNCTION Strip( in_str, cExtra )

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))

ChatGPT said:
ChatGPT
To convert the Xbase++ function Strip to PL/pgSQL, we will create a PL/pgSQL function that mimics the behavior of the original function. Here’s how you can do it:

Xbase++ Function Breakdown
Parameters: Takes an input string and an optional string of extra characters to strip.
Local Variables: Initializes junk characters and other variables.
Checks for empty input: Returns an empty string if the input is empty.
Concatenates junk characters: Adds any extra characters to the junk list.
Iterates through the input string: Builds a new string excluding junk characters.
Returns the result in uppercase.
PL/pgSQL Equivalent
Here’s how you can write the equivalent function in PL/pgSQL:

CREATE OR REPLACE FUNCTION Strip(in_str TEXT, cExtra TEXT)
RETURNS TEXT AS $$
DECLARE
xJunk TEXT := '- .,/;\|()&';
z TEXT := '';
x INT;
y INT;
BEGIN
IF in_str IS NULL OR in_str = '' THEN
RETURN '';
END IF;

-- Add extra characters to junk list if provided
IF cExtra IS NOT NULL AND cExtra <> '' THEN
xJunk := xJunk || cExtra;
END IF;

-- Trim the input string
in_str := TRIM(in_str);
x := LENGTH(in_str);

-- Iterate through each character in the input string
FOR y IN 1..x LOOP
-- Check if the character is not in the junk list
IF POSITION(SUBSTRING(in_str FROM y FOR 1) IN xJunk) = 0 THEN
z := z || SUBSTRING(in_str FROM y FOR 1);
END IF;
END LOOP;

RETURN UPPER(z);
END;
$$ LANGUAGE plpgsql;

Explanation
Function Definition: We define the function Strip that takes two parameters: in_str and cExtra.
Variable Declaration: We declare necessary variables, including the xJunk string and the result string z.
Empty Input Check: We check if the input string is empty or NULL.
Concatenation of Junk Characters: If cExtra is provided, we append it to xJunk.
Trimming Input: We use TRIM to remove leading and trailing spaces from in_str.
Character Iteration: We loop through each character in the trimmed string, checking if it's not in the junk characters.
Building the Result: Valid characters are concatenated to z.
Return: Finally, we return the uppercased result.
Usage
You can call this function in your PostgreSQL queries like this:

sql
Copy code
SELECT Strip('Hello, World! 123', '123');
This will strip the specified characters and return the result. If you have any more specific needs or further adjustments, feel free to ask!

ChatG

[Tom]

In germany, we say: This is just the top of the iceberg.