user defined function

Wayne_1

I need to create some user defined function in Vertica by sql. It looks like Vertica can support UDF. Do you have any document about that? Thanks.

Said_Montiel

Vertica has its own documentation with examples and explanations. Look for Programmer/SQL/Admin Reference Guides you can look for them on site (depending on the version) or Google them.

What language are you looking for?

Wayne_1

I am looking for UDF by SQL. for example

CREATE or REPLACE FUNCTION My_UDF(input int)
AS
DECLARE
   v_char  varchar(64)
BEGIN
     v_char = to_char(input);
     return  v_char;
END;
/

Said_Montiel

I think you could use this

https://community.vertica.com/vertica/topics/since_stored_procedure_is_not_supported_by_vertica_so_w...

It's a stored procedure since a UDF (from what I understand) is a script adapted from another language (C++, Java or R)

Navin_C

Hi Wayne,

Replicating the above example shown by you as in Vertica standard.

If you need something like this, you can write a UDF SQL function like this:

CREATE FUNCTION PRINT_INPUT(x varchar) RETURN VARCHAR
AS
BEGIN
RETURN TO_CHAR(x);
END;

Hope this helps.

Rahul_Choudhary

Hi   I believe you are looking for something like this.  As per Vertica documention a user-defined SQL function can be used anywhere in a query where an ordinary SQL expression can be used, except in the table partition clause or the projection segmentation clause. To create a SQL function, the user must have CREATE privileges on the schema. To use a SQL function, the user must have USAGE privileges on the schema and EXECUTE privileges on the defined function.  This following statement creates a SQL function called myzeroifnull that accepts an INTEGER argument and returns an INTEGER result.  => CREATE FUNCTION myzeroifnull(x INT) RETURN INT    AS BEGIN       RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END);     END;  Will suggest you to kindly go through below  mentioned Vertica documented link for detailed info on this & certainly can help you.  https://my.vertica.com/docs/6.1.x/HTML/index.htm#15042.htm   Regards Rahul  

Wayne_1

Thanks, Rahul ! The document you mentioned is very useful. Do you have any more docs about a little bit more complicate user defined functions, which includes more controls, like loop, if - then -else, cursor? thanks in advance !