This was needed to create a PostGreSql stored function that can be used in SELECT queries.
The resultant code worked perfectly.
I had it also convert 4 other functions and there was only 1 error in which it didn't understand a call to Strip() and interpreted it as Alltrim().
All I had to do to fix that was put the Strip() back.
Tom Liehr advised me to use ChatGPT and I was skeptical, but not any more.
I am looking to extend my programming career by taking on some PostGreSql conversions.
This is very promising for developers who are currently using ADS, which is being discontinued at the end of this year.
Alaska Software claims that these migrations should require a minimal amount of work when using ISAM mode, however I found a few issues in some multitier seek algorithms that affected performance, but those routines were easily converted to SQL SELECT statements. Don't be afraid of SQL, it is fully compatible with your existing code. You only use it when you need more performance in a particular area of the application. For example, I worked on a large application that downloaded a lot of data from taxi drivers in NYC many times per day. That used to take a long time to write out all that data until I put it into an array and just wrote a routine to convert the contents of the array to a SQL INSERT statement.
Here was what I gave to ChatGPT:
Code: Select all
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(in_str[y]$xJunk,"",in_str[y]) // strip out the junk
NEXT
RETURN(upper(z))Code: Select all
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!
