Viewing function definitions in Vertica

I don't know if you have tried this. But I did try to read function definition from the system tables in Vertica.

I used the table called USER_FUNCTIONS which is a table in the V_CATALOG system schema. More details can be found here: https://my.vertica.com/docs/6.1.x/HTML/index.htm#15021.htm

But unfortunately when I try and do something like this:

1vertdeva01:20130604-113434 > select function\_definition from v\_catalog.user\_functions where schema\_name='calc' and function\_name='getCYForDate';  
2\----------------------------------------------------------------------------------------------------------------------------------  
3RETURN to\_date(concat(((((date\_part('year', (processDate)::timestamp))::int - CASE WHEN ((date\_part('month', (processDate)::time  
4(1 row)  
5

If you notice the function definition is only partly visible. The rest of it gets truncated. I don't know how to set it right. If you know how, feel free to comment on this post.

After some googling I realized that there was another way to check out the source of an existing user defined function in Vertica.

You could use EXPORT_OBJECTS(). It is a Vertica Meta function that can be used to create scripts of existing objects that can be then used on another cluster to recreate it.
More information about it (the arguments it takes and other options) here: https://my.vertica.com/docs/6.1.x/HTML/index.htm#12721.htm

So when I tried using that function:

1vertdeva01:20130604-113916 > select export\_objects('','calc.getCYForDate');  
2\----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
3CREATE FUNCTION calc.getCYForDate(cy Integer, processdate Date)  
4RETURN date AS  
5BEGIN  
6RETURN to\_date(concat(((((date\_part('year', (processDate)::timestamp))::int - CASE WHEN ((date\_part('month', (processDate)::timestamp))::int > 2) THEN 1 ELSE 2 END) + cy))::varchar, '1231'), 'YYYYMMDD');  
7END;  
8SELECT MARK\_DESIGN\_KSAFE(2);  
9(1 row)  

I finally got to see the function definition. But I agree it is not as how I wrote it. The CAST(field AS datatype) syntax is translated into (field)::datatype. But I'm glad at least there is a way to view user function definitions from vsql.

comments powered by Disqus