All Forums Database
juanalfonso 41 posts Joined 01/16
16 Feb 2016
How to create a function similar to Oracle (or a procedure with a return value)?

How to create a function similar to Oracle (or a procedure with a return value)?

Hello everyone,

Functions defined by the user in Teradatab seem to be quite different to the concept learnt in Oracle.

I would like to know how to create a funcion that receives parameters and return a value. Something like this Oracle one:

FUNCTION F_QUITA_REPES (cCadena IN varchar2, delchar IN char) RETURN varchar2 IS
   
  cSubCad VARCHAR2(252) := cCadena;
 
BEGIN
 
  IF delchar = chr(39) THEN
    cSubCad := LTRIM(cSubCad, delchar);
  ELSE
    cSubCad := LTRIM(RTRIM(cSubCad, delchar), delchar);
  END IF;
   
  IF cSubCad IS NOT NULL THEN
    WHILE cSubCad LIKE '%'||delchar||delchar||'%' LOOP
      cSubCad:=REPLACE(cSubCad, delchar||delchar, delchar);
    END LOOP;
  END IF;
   
  RETURN(cSubCad);
 
END F_QUITA_REPES;

That later can be called inside another function or procedure like:

cCadNomVia := F_QUITA_REPES(cCadNomVia, '/');

Or like:

IF F_QUITA_REPES(cCadNomVia, '/') is null and F_QUITA_REPES(cCadNomVia, ' ') is null THEN

Cos I think Stored Procedures can't have a return value, right?

Also, I would need to be able to have OUT and INOUT parameters in the functions...

Thanks in advance and best regards

 

dnoeth 4628 posts Joined 11/04
17 Feb 2016

SQL functions in Teradata are very limited, no SELECT, loops, etc. For complex logic you need C or Java.
 
But in your case the logic can easily be rewritten using regex, now it's one code block and this can be a UDF:

REPLACE FUNCTION F_QUITA_REPES (cCadena VARCHAR(255), delchar VARCHAR(1)) 
RETURNS VARCHAR(255)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
   REGEXP_REPLACE(CASE
                    WHEN delchar = CHR(39) 
                      THEN LTRIM(cCadena, delchar)
                    ELSE LTRIM(RTRIM(cCadena, delchar), delchar)
                  END, delchar||'+', delchar)
;

Caution, a VARCHAR without length is a VARCHAR(1) in Teradata.

Dieter

juanalfonso 41 posts Joined 01/16
17 Feb 2016

Ok Dieter, thanks for the information!

Anyway, I have functions more complex than that in Oracle to translate (and also, sometimes is easier just to try a translation of code and not a big change), so I guess a good option could be to convert functions to procedures, right?

The annoying thing would be the use of the new procedures for getting results to catch on the fly, like the examples I exposed before:

variable := FUNCTION(a, b);
IF FUNCTION_01(a,b) > 3 and FUNCTION_02(b,c) > 5 THEN ...

I guess an alternative, after converting the functions to procedures, could be to create auxuliar variables and do things like:

DECLARE var1 INTEGER;
DECLARE var2 INTEGER;
 
CALL FUNCTION_01(a, b, var1); -- I add to the new procedure the return value of the olf function as an OUT parameter
CALL FUNCTION_02(b, c, var2);
 
IF var1 > 3 and var2 > 5 THEN ...

What do you think about this? ... Because procedures in Teradata can't have a return value, can they?

Regards,

 

dnoeth 4628 posts Joined 11/04
17 Feb 2016

SPs only return OUT parameters (or result sets), but not a single value like a function, so you can't use it directly and your proposed alternative is the only way.

Dieter

juanalfonso 41 posts Joined 01/16
17 Feb 2016

Ok, thanks again Dieter!

By the way, the function you made has only one part of the code I exposed before, the first IF-THEN-ELSE, but not the second IF with the WHILE inside.

According to what I've read, functions can only seem to have one RETURN sencence (and no loops either).

So I guess there is not a way to convert the Oracle function I exposed to a Teradata function (at least, not simple and clear), appart from converting it into a procedure and apply the method I suggested...

Because... Is there a way to do an intermediat step in a Teradata function, like declaring an auxiliar variable with the value of the firts IF-THEN-ELSE and then use it for the RETURN?

dnoeth 4628 posts Joined 11/04
17 Feb 2016

The function I posted does both parts, the REGEXP_REPLACE replaces multiple occurrences of a character with a single.
You could do the same in Oracle.

Dieter

juanalfonso 41 posts Joined 01/16
17 Feb 2016

Ah ok! I didn't look at it well before. Sorry!
Just to finish about this issue, in case I needed... Is there a way to do an intermediat step in a Teradata function, like declaring an auxiliar variable with the value of an operation and then use it for the RETURN? For not doing always everything in one single big return sentence...

dnoeth 4628 posts Joined 11/04
17 Feb 2016

No declared variables, no steps.
In fact I'm glad that no complex things are allowed, otherwise people would start writing stupid UDFs with loops/Selects, etc. and then try to use them on that 10 billion row table :-)

Dieter

juanalfonso 41 posts Joined 01/16
17 Feb 2016

Haha, that's true... I'm included sometimes ;)
As a curiosity, in the function you made, I've found a character that I can't introduce for delchar: the question mark as a literal. It doesn't get it as '?', '\?' or even chr(63)...

dnoeth 4628 posts Joined 11/04
17 Feb 2016

Oops, of course, this is a regex meta character, forgot to escape it:

REPLACE FUNCTION F_QUITA_REPES (cCadena VARCHAR(255), delchar VARCHAR(1)) 
RETURNS VARCHAR(255)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
   REGEXP_REPLACE(CASE
                    WHEN delchar = CHR(39) 
                      THEN LTRIM(cCadena, delchar)
                    ELSE LTRIM(RTRIM(cCadena, delchar), delchar)
                  END, '\'||delchar||'+', delchar)
;

 

Dieter

juanalfonso 41 posts Joined 01/16
17 Feb 2016

That way can generates some conflict if the character in delchar is a number, "n", "\n", "t" or "\t", for example. I guess it's quite difficult to make it work for everything.

Anyway, for the characters I usually use on this function (space, -, /, \ and apostrophe), it works ok like that.

Thanks and regards

dnoeth 4628 posts Joined 11/04
17 Feb 2016

There's alway a regex :-)
 

REPLACE FUNCTION F_QUITA_REPES (cCadena VARCHAR(255), delchar VARCHAR(1)) 
RETURNS VARCHAR(255)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
   REGEXP_REPLACE(CASE
                    WHEN delchar = CHR(39) 
                      THEN LTRIM(cCadena, delchar)
                    ELSE LTRIM(RTRIM(cCadena, delchar), delchar)
                  END, '([\'||delchar||'])\1+', '\1')
;

(           # beginning of 1st capturing group
  [         # beginning of characters class
     \      # escape the search character
  ]         # end of character class
)           # end of 1st capturing group
\1          # back reference to 1st capturing group
+           # one or more repeated characters

 
I'm shure the TRIMs could be included also, but this is a task for someone who knows regexes better than me :-)

Dieter

juanalfonso 41 posts Joined 01/16
18 Feb 2016

Ok Dieter, thanks again! Anyway, that already was enough for me.

But also, I wanted to add some reflexion... The fact that TRIM's and REGEXP's are living together in the same function can cause some conflicts (with numbers or a "n" or a "t"), because TRIM's don't use regular expression but REGEXP's do. So being inspired by your ideas, I've translated TRIM's into REGEXP's (and delchar now has length 2 in case you want to add the escape for the character) and now conflicts are gone.

REPLACE FUNCTION F_QUITA_REPES (cCadena VARCHAR(255), delchar VARCHAR(2)) RETURNS VARCHAR(255) 
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
  REGEXP_REPLACE(
    CASE
      WHEN delchar = CHR(39) THEN REGEXP_REPLACE(cCadena, '^('||delchar||'+)(.*)$', '\2') -- LTRIM
      ELSE REGEXP_REPLACE(cCadena, '^('||delchar||'*)([^'||delchar||']?.*[^'||delchar||'])?('||delchar||'*)$', '\2') -- TRIM
    END,
    delchar||'+', delchar)
;

Anyway, if I could have chosen, I would have prefered the function with TRIM's and a specie of recursive OREPLACE, to treat literals just as literals in this case and do "OREPLACE(cSubCad, delchar||delchar, delchar)" til there is not consecutive repetitions of delchar. But I guess that doesn't exist...

Regards

 

You must sign in to leave a comment.