Example sql execution syntax in TransformFunction

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

[Deleted User]

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

Thanks, I'll take a look.

stevejj

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.