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

Use the SQL preproccesor to support postgreSQL/MySQL functions (e.g. DATE )

    Details

    • Type: How To
    • Status: Obsolete
    • Affects Version/s: EXASolution 4.2.0, EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.x
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Solution:
      Hide

      Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base:

      https://community.exasol.com/t5/database-features/using-the-sql-preprocessor-to-support-postgresql-mysql-functions/ta-p/1041

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

      Background

      Some functions from other databases are not supported by Exasol. But with the powerful SQL preprocessor framework, you can easily extend the SQL language of Exasol.

      Explanation

      The following simple example shows how the DATE function from postgreSQL/ MySQL is mapped to TO_DATE:

      CREATE OR REPLACE LUA SCRIPT "POSTGRES2EXA" () RETURNS ROWCOUNT AS
      
      import('TRANSFORMATIONS', 'TRANSFORMATIONS')
      
      -- get SQL text
      sqltext = sqlparsing.getsqltext()
      
      --Perform transformations
      newsqltext = TRANSFORMATIONS.transformDatetoDate(sqltext)
      
      -- set thew new SQL text
      sqlparsing.setsqltext(newsqltext)
      
      /
      
      CREATE OR REPLACE LUA SCRIPT "TRANSFORMATIONS" () RETURNS ROWCOUNT AS
      
      function transformDatetoDate(sqltext)  
        while(true) do
          local tokens = sqlparsing.tokenize(sqltext)
        
          found = sqlparsing.find(tokens, 1, true, false, sqlparsing.iswhitespaceorcomment, 
                                  'DATE', '(')
        
          if (found==nil) then
            break;
          end
      
          local ifEnd = sqlparsing.find(tokens, found[2],true, true, sqlparsing.iswhitespaceorcomment, ')')
      
          if (ifEnd==nil) then
              error("date function not properly ended")
          end
      	
          sqltext = table.concat(tokens, '', 1, found[1]-1)..'TO_DATE ('..table.concat(tokens, '', found[2]+1)
      
        end
      
        return sqltext
      
      end
      
      /
      

      To activate the preprocessor script for the current session, please use the following command:

      alter session set SQL_PREPROCESSOR_SCRIPT = <MYSCHEMA>.POSTGRES2EXA;
      
      Show
      Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base: https://community.exasol.com/t5/database-features/using-the-sql-preprocessor-to-support-postgresql-mysql-functions/ta-p/1041 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Background Some functions from other databases are not supported by Exasol. But with the powerful SQL preprocessor framework, you can easily extend the SQL language of Exasol. Explanation The following simple example shows how the DATE function from postgreSQL/ MySQL is mapped to TO_DATE: CREATE OR REPLACE LUA SCRIPT "POSTGRES2EXA" () RETURNS ROWCOUNT AS import( 'TRANSFORMATIONS' , 'TRANSFORMATIONS' ) -- get SQL text sqltext = sqlparsing.getsqltext() --Perform transformations newsqltext = TRANSFORMATIONS.transformDatetoDate(sqltext) -- set thew new SQL text sqlparsing.setsqltext(newsqltext) / CREATE OR REPLACE LUA SCRIPT "TRANSFORMATIONS" () RETURNS ROWCOUNT AS function transformDatetoDate(sqltext) while ( true ) do local tokens = sqlparsing.tokenize(sqltext) found = sqlparsing.find(tokens, 1, true , false , sqlparsing.iswhitespaceorcomment, ' DATE ' , '(' ) if ( found ==nil) then break ; end local ifEnd = sqlparsing.find(tokens, found [2], true , true , sqlparsing.iswhitespaceorcomment, ')' ) if (ifEnd==nil) then error( " date function not properly ended" ) end sqltext = table .concat(tokens, '', 1, found [1]-1)..' TO_DATE ( '.. table .concat(tokens, ' ', found [2]+1) end return sqltext end / To activate the preprocessor script for the current session, please use the following command: alter session set SQL_PREPROCESSOR_SCRIPT = <MYSCHEMA>.POSTGRES2EXA;
    • Category 1:
      SQL - SQL preprocessor
    • Category 2:
      Scripting

      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: