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:
vertdeva01:20130604-113434 > select function\_definition from v\_catalog.user\_functions where schema\_name='calc' and function\_name='getCYForDate';
\----------------------------------------------------------------------------------------------------------------------------------
RETURN to\_date(concat(((((date\_part('year', (processDate)::timestamp))::int - CASE WHEN ((date\_part('month', (processDate)::time
(1 row)
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:
vertdeva01:20130604-113916 > select export\_objects('','calc.getCYForDate');
\----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION calc.getCYForDate(cy Integer, processdate Date)
RETURN date AS
BEGIN
RETURN 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');
END;
SELECT MARK\_DESIGN\_KSAFE(2);
(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.