Nested Stored Procedure Support in Vertica

Abhishek_Chaudh

We are migrating our product from Sybase IQ to Vertica. We have lot of stored procedures written in Sybase. As Vertica doesn't support stored procedures, we developed few Java UDx which are working as fine. 

We are now facing few problems as we are moving forward with migration, lot of our stored procedures are very complex and nested. Nested as in few of the procedure are calling other procedures. 

How can we replicate similar functionality using Java UDx? Say there are two Java UDXs, A and B. How exactly B can be called by A? How code reusability is achieved. 

One little additional question along with above. There are several types of procedures, few accepting in, out and inout parameters. How can we write such a UDx in Vertica?

Quick response will be highly appreciated.

Thanks,
Abhishek

id10t

Hi!

How can we replicate similar functionality using Java UDx? Say there are two Java UDXs, A and B. How exactly B can be called by A? How code reusability is achieved.

Unfortunately you can't, but you can open a feature request, or as alternative you can achieve it via JDBC and ODBC.

Disclaimer: it's not an official response(Im not Vertica employee), just an user2user help.

Abhishek_Chaudh

It's very simple. Answer is quite logical. Create your procedure B, register it. Create procedure A and simply call B using select B() using JDBC and get data in resultset.

[Deleted User]

The following mechanism of  nesting may work depending on your procedures

select procedureA ( procedureB (col1, col2), col3) from Table T;


this is akin to
select Avg( Sum(c1,c2), c3) from Table T;