Example sql execution syntax in TransformFunction
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
0
Comments
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
Thanks, I'll take a look.
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.