Performing SQL Queries in UDFs

Moderator: NorbertKrupa

Post Reply
SThak
Newbie
Newbie
Posts: 10
Joined: Wed Jul 24, 2013 10:30 pm

Performing SQL Queries in UDFs

Post by SThak » Fri Aug 23, 2013 6:53 pm

Can queries like these be performed in UDFs to make the execution faster?

Code: Select all

vsql -d VMart_Schema -w ${DB_PASSWD} -c "INSERT INTO GL.CURRENCYTABLE (CURRENCY_CODE, PERIOD_YEAR, BEGIN_BALANCE_DR,INDIAN_CURRENCY) SELECT 
	 CURRENCY_CODE, 
	 PERIOD_YEAR,  
	 BEGIN_BALANCE_DR,  
         (CASE  
		WHEN CURRENCY_CODE = 'GBP' THEN BEGIN_BALANCE_DR*95  
		WHEN CURRENCY_CODE = 'CAD' THEN BEGIN_BALANCE_DR*60 
	  ELSE 
		BEGIN_BALANCE_DR 
	 END ) as INDIAN_CURRENCY 
     FROM 
	GL.GL_BALANCES;COMMIT; "
[/color]

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: performing sql queries in UDFs

Post by scutter » Sat Aug 24, 2013 8:14 pm

You wouldn't run SQL from a UDF. UDFs are intended for computational extensions for operations that aren't supported out of the box by Vertica.

If a query is underperforming, it's most likely a projection design issue. Try Database Designer with sample queries to create projections. This is an INSERT, so be sure to have only as many projections on the table as you need - more projections means longer load times.

--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Post Reply

Return to “Vertica User Defined Functions (UDFs)”