Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-172

User-defined scalar functions (pl/SQL style)

    Details

    • Type: Explanation
    • Status: Obsolete
    • Affects Version/s: EXASolution 4.2.0, EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Explanation:
      Hide

      Note: This solution is no longer maintained. For the most up-to-date information, please see our documentation portal: https://docs.exasol.com/sql/create_function.htm

      ---------------------------------------------------------------------------------------------------------------------------------------------

      EXASolution does support scalar user-defined pl/SQL functions which compute a result value depending on their input parameters.
      For more complex user defined functions use EXAPowerlytics

      An execution of SQL statements inside those functions is not possible (contrary to scripts).

      Example for a simple function

      CREATE OR REPLACE FUNCTION mymax(a IN INT,b IN INT) RETURNS int
      AS res INT;
      BEGIN
      IF (a>b) then    
              res:=a;
      else   
              res:=b;
      end IF;
      RETURN res;
      END;
      /
               
      SELECT mymax(123, 8892) FROM dual;
      

      Manual calculation of date-difference

      CREATE OR REPLACE FUNCTION examples.days_between(a IN DATE, b IN DATE) RETURNS INTEGER
      IS
          num INTEGER;
          swap DATE;
      BEGIN
          num := 0;
          -- Ensure that a<=b
          IF( a>b ) then
              swap := a;
              a := b;
              b := swap;
          end IF;
          -- Repeatedly add days.
          while( a<b ) do
              a := a+1;
              num := num+1;
          end while;
          RETURN num;
      END;
      /
       
      SELECT examples.days_between(sysdate, '2009-05-01') FROM dual;
      
      

      Contrary to the built-in function days_between, this user-defined function always returns a positive integer.

      Also note that functions that overload builtin functions need to be called using a fully-qualified identifier.

      Furthermore you can also use EXASolution built-in scalar functions inside your function.

      Show
      Note: This solution is no longer maintained. For the most up-to-date information, please see our documentation portal:  https://docs.exasol.com/sql/create_function.htm --------------------------------------------------------------------------------------------------------------------------------------------- EXASolution does support scalar user-defined pl/SQL functions which compute a result value depending on their input parameters. For more complex user defined functions use EXAPowerlytics An execution of SQL statements inside those functions is not possible (contrary to scripts). Example for a simple function CREATE OR REPLACE FUNCTION mymax( a IN INT ,b IN INT ) RETURNS int AS res INT ; BEGIN IF ( a >b) then res:= a ; else res:=b; end IF ; RETURN res; END ; / SELECT mymax(123, 8892) FROM dual ; Manual calculation of date-difference CREATE OR REPLACE FUNCTION examples.days_between( a IN DATE , b IN DATE ) RETURNS INTEGER IS num INTEGER ; swap DATE ; BEGIN num := 0; -- Ensure that a <=b IF ( a >b ) then swap := a ; a := b; b := swap; end IF ; -- Repeatedly add days. while ( a <b ) do a := a +1; num := num+1; end while ; RETURN num; END ; / SELECT examples.days_between( sysdate , '2009-05-01' ) FROM dual ; Contrary to the built-in function days_between, this user-defined function always returns a positive integer. Also note that functions that overload builtin functions need to be called using a fully-qualified identifier. Furthermore you can also use EXASolution built-in scalar functions inside your function.
    • Category 1:
      SQL

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: