Skip to main content

Example sql execution syntax in TransformFunction

  • July 14, 2015
  • 3 replies
  • 21 views
stevejj

Hi,

Could someone point me on an example of a transform function (R or Java is fine) which includes SQL against tables from the calling SQL please ... I can see various examples with errors but nothing which is valid plus I don't see how the tables in originating SQL select are referred to.

 

 

Eg for a query SELECT udft_function FROM table a INNER JOIN b on a.key = b.foreign_key what does the code in udft_function see ?

can it execute a query like SELECT a.col1, b.col1 FROM table a INNER JOIN b on a.key = b.foreign_key ?

 

Thanks

    3 replies

    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • July 14, 2015

    You can find examples in the Vertica installation :

     

    [dbadmin@emoreno1 dist]$ cd /opt/vertica/sdk/examples/
    [dbadmin@emoreno1 examples]$ ls -al
    total 136
    drwxr-xr-x 16 dbadmin verticadba  4096 Jul 10 22:51 .
    drwxr-xr-x  6 dbadmin verticadba  4096 Jul 10 22:51 ..
    drwxr-xr-x  2 dbadmin verticadba  4096 Jul 10 22:51 AggregateFunctions
    -rw-r--r--  1 dbadmin verticadba  1533 Jul  3 00:54 AggregateFunctions.sql
    drwxr-xr-x  2 dbadmin verticadba  4096 Jul 10 22:51 AnalyticFunctions
    -rw-r--r--  1 dbadmin verticadba  2100 Jul  3 00:54 AnalyticFunctions.sql
    drwxr-xr-x  2 dbadmin verticadba  4096 Jul 10 22:51 ApportionLoadFunctions
    -rw-r--r--  1 dbadmin verticadba  2165 Jul  3 00:54 ApportionLoadFunctions.sql
    drwxr-xr-x  3 dbadmin verticadba  4096 Jul  6 15:51 build
    drwxr-xr-x  2 dbadmin verticadba  4096 Jul 10 22:51 data
    drwxr-xr-x  2 dbadmin verticadba  4096 Jul 10 22:51 FilterFunctions
    -rw-r--r--  1 dbadmin verticadba  3135 Jul  3 00:54 FilterFunctions.sql
    drwxr-xr-x  2 dbadmin verticadba  4096 Jul 10 22:51 HelperLibraries
    -rw-r--r--  1 dbadmin verticadba  2065 Jul  3 00:54 JavaFunctions.sql
    -rw-r--r--  1 dbadmin verticadba  1323 Jul  3 00:54 JavaUDLFunctions.sql
    drwxr-xr-x  5 dbadmin verticadba  4096 Jul  3 00:57 JavaUDx
    -rw-r--r--  1 dbadmin verticadba  1515 Jul  3 00:54 LICENSE
    -rw-r--r--  1 dbadmin verticadba 13997 Jul  3 00:54 makefile
    drwxr-xr-x  3 dbadmin verticadba  4096 Jul 10 22:51 ParserFunctions
    -rw-r--r--  1 dbadmin verticadba  2878 Jul  3 00:54 ParserFunctions.sql
    drwxr-xr-x  2 dbadmin verticadba  4096 Jul 10 22:51 PloadFunctions
    -rw-r--r--  1 dbadmin verticadba  3452 Jul  3 00:54 README.txt
    drwxr-xr-x  2 dbadmin verticadba  4096 Jul 10 22:51 RFunctions
    -rw-r--r--  1 dbadmin verticadba  1099 Jul  3 00:54 RFunctions.sql
    drwxr-xr-x  2 dbadmin verticadba  4096 Jul 10 22:51 ScalarFunctions
    -rw-r--r--  1 dbadmin verticadba  3123 Jul  3 00:54 ScalarFunctions.sql
    drwxr-xr-x  3 dbadmin verticadba  4096 Jul 10 22:51 SourceFunctions
    -rw-r--r--  1 dbadmin verticadba  1924 Jul  3 00:54 SourceFunctions.sql
    drwxr-xr-x  2 dbadmin verticadba  4096 Jul 10 22:51 TransformFunctions
    -rw-r--r--  1 dbadmin verticadba  6763 Jul  3 00:54 TransformFunctions.sql

     

     

    stevejj
    • stevejj
    • Author
    • New Participant
    • July 15, 2015

    Thanks, I'll take a look.

    stevejj
    • stevejj
    • Author
    • New Participant
    • July 16, 2015

    Apologies but I can't see a relevant here example here.

     

    I'm looking to use a transform function to build a result set for a calling report rather than leaving it (Tableau) to put all the data in and then process it. The concern here is the amount of data which would be pulled into Tableau before processing, plus security of the data.

     

    Is there an example of a transform function which either preferrably queries data itself from Vertica, or uses the rows (not an individual column) supplied by the calling SQL, filters/processes it, and then returns its own result set ?

     

    Thanks again.

     

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement