how can vertica execute sql in sql function
CREATE FUNCTION UTC_TO_LOC(x DATETIME) RETURN DATETIME
AS BEGIN
RETURN (SELECT timestampadd('mi',base_utc_offset,x) from dwmetadata.timezone_info where year_=1956);
END ;
why the above statement doesn't work in vertica ? how can vertica execute sql in sql function?
AS BEGIN
RETURN (SELECT timestampadd('mi',base_utc_offset,x) from dwmetadata.timezone_info where year_=1956);
END ;
why the above statement doesn't work in vertica ? how can vertica execute sql in sql function?
0
Comments
From Version 6, usage of SELECT queries in Vertica SQL UDF is restricted.
Only RETURN statements are allowed in SQL UDF.
Let us know your requirements, might be we can help you .
Hope this helps
NC
Very appreciate your reply , can i understand that Vertica SQL UDF would not support usage of SELECT queries even at V7 , right ?
So just like the case i show , if i want to package some SQL queries into a black box ,so users can use it very easy , how can do it ?
is that the only way to use external procedure or Java or R -UDF ? Actually , i read some docs from Vertica online site, is that correct that if i use SQL query in the external procedure or R-UDF , i need create a connection every time using JDBC when the function is called every time ? If like this , that should be very bad performance and consumption of meaningless process .
Hi all,
I also need the answer. Does that realy mean that UDFs in Vertica cannot use SELECT or FROM statements?
I there any alternative of composing a black box function (name a different object if you will) and use it in the where clause of a different query?