How to see a procedure or function structure in vertica?


We can see the table structure using \d schema_name.table_name in vsql.

But how to view a procedure or functions structure in vertica?
\d schema_name.proc_func_name() doesnt work.



  • Hi Sundeep, Hm... Could you be more specific about what you are trying to do? In particular, most functions in Vertica are not written in a SQL-like language. Many are written in Java or C++. You probably really want the function's source code, which is not available through Vertica in that case, but is sometimes available online. Adam
  • Hi

    At vsql you can simple  type \? , it will list you all the relevant  help and  catalog retrieve options , your specific case can be answer by typing :



  • Hi!

    Function example here:

    daniel=> CREATE OR REPLACE FUNCTION is_int(a varchar(65000))
    daniel-> RETURN BOOLEAN
    daniel-> AS BEGIN
    daniel-> RETURN
    daniel-> REGEXP_LIKE(TRIM(a),'^\d{1,19}$')
    daniel-> AND
    daniel-> ABS(a::NUMERIC) < 9223372036854775808; -- check range
    daniel-> END;

    daniel=> select function_name, procedure_type, function_definition from user_functions where function_name = 'is_int';
     function_name |    procedure_type     |                                       function_definition                                       
     is_int        | User Defined Function | RETURN (regexp_like(btrim(a), E'^\\d{1,19}$', '') AND (abs((a)::numeric) < 9223372036854775808))
    (1 row)
  • Thanks
    But what i want is how to view the definition of the function/procedure.

    And also not the UDF but also the internal system defined functions/procedures.


    SELECT REFRESH('table_name');

    dbadmin> \df REFRESH
                             List of functions
     procedure_name | procedure_return_type | procedure_argument_types
     refresh        | Varchar               |
     refresh        | Varchar               | Varchar
    (2 rows)
    dbadmin> \df ANALYZE_STATISTICS
                               List of functions
       procedure_name   | procedure_return_type | procedure_argument_types
     analyze_statistics | Integer               | Varchar
    (1 row)
                                  List of functions
          procedure_name      | procedure_return_type | procedure_argument_types
     set_optimizer_directives | Varchar               | Varchar
    (1 row)

    dbadmin> select function_name, procedure_type, function_definition from user_functions where function_name = 'ANALYZE_STATISTICS';
     function_name | procedure_type | function_definition
    (0 rows)
    dbadmin> select function_name, procedure_type, function_definition from user_functions where function_name = 'REFRESH';
     function_name | procedure_type | function_definition
    (0 rows)
    dbadmin> select function_name, procedure_type, function_definition from user_functions where function_name = 'SET_OPTIMIZER_DIRECTIVES';
     function_name | procedure_type | function_definition
    (0 rows)


  • Hi Sundeep,

    Are you looking for the source code of these functions, or just their type signatures?

    It looks like you have found their type signatures.

    As for the source code -- these functions, like most built-in functions in Vertica, are implemented directly in C++. So to see the structure, you woul have to see Vertica's source code. Vertica is not open-source, so this may be somewhat difficult...

    What, exactly, are you trying to do? Why are you trying to see the structure of these functions?

  • i want to see what parameters does the function/rpocedure take.
    for example:
    SET_OPTIMIZER_DIRECTIVES(xxxxxx)  function  takes a parameter AvoidUsingProjections.

    How can i know that this function has this parameter and what datatype this parameter value is.


  • Navin_CNavin_C Vertica Customer
    Hi Sundeep,

    It will be interesting to know where to find the allowed  parameters for these function

    However, you can know more about the other documented configuration parameters and the function here.
    All the parameters are not described in the Documents, but you can surely explore the database for this.

    SET_CONFIGURATION_PARAMETERS('parameter_name', 'value')
    nnani=> select parameter_name, description from configuration_parameters limit 10;         parameter_name       |                                            description
     ActivePartitionCount       | No of active partitions
     AdvanceAHMInterval         | Interval between advancing the AHM (seconds)
     AHMBackupManagement        | Consider backup epochs when setting new AHM
     AnalyzeRowCountInterval    | Interval between Tuple Mover row count statistics updates (seconds)
     AnalyzeStatsPlanMaxColumns | Maximum number of columns to analyze with each analyze stats plan
     AnalyzeStatsSampleBands    | Number of sampling bands to use when not using entire data set.
     AuditConfidenceLevel       | The confidence level at which to run audits of license size utilization. Represent 99.5% as 99.5.
     AuditErrorTolerance        | The error tolerance for audits of license size utilization. Represent 4.5% as 4.5.
     BasicVerticaOptions        | Use as recommended by Technical Support
     BlockCacheSize             | size of memory managed by memory manager (in MB)
    (10 rows)
    These all parameters are used with SET_CONFIGURATION_PARAMETERS Function in Vertica.

    Hope this helps

  • Hi,

    Set set_vertica_options -

    This can be done on a query level using the add_vertica_options hint , below is an example of how to set one of the Vertica options on the query level , the concept should be same for all others   :

    One of the Vertica  options that was  categorize as a basic is DISABLE_SIPS , using the below method your will be able to enable this parameter on a query level :


    SELECT /*+add_vertica_options(BASIC,DISABLE_SIPS)*/ DISTINCT unique_id FROM <your table>

    running this query you will see all the options :

    select show_all_vertica_options()

  • Navin_CNavin_C Vertica Customer
    Superlike !!!

    Any idea as to what is the description for all these parameters.

    Similarly any clues on SET_OPTIMIZER_DIRECTIVES function

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file