How to see a procedure or function structure in vertica?

sunkupuli

Hi

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.

Thanks
Sundeep

[Deleted User]

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

eli_revach

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 :

\df

Thanks 

Daniel_Leybovic

Hi!


Function example here: https://github.com/sKwa/vertica/blob/master/UDFSQL/sql_funcs.sql


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;
CREATE FUNCTION


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)

sunkupuli

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.

Examples:

SELECT REFRESH('table_name');
SELECT ANALYZE_STATISTICS('table_name');
SELECT SET_OPTIMIZER_DIRECTIVES('parameterxxxx');


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

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>
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)
dbadmin>


Thanks
Sundeep

[Deleted User]

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?

Adam

sunkupuli

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.

Thanks
Sundeep

Navin_C

Hi Sundeep,

It will be interesting to know where to find the allowed  parameters for these function
set_vertica_options()
set_optimizer_directives()

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
NC

eli_revach

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_C

Superlike !!!

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

Similarly any clues on SET_OPTIMIZER_DIRECTIVES function