How to create external procedures in Vertica

How do I create functions / procedures in Vertica that make use of SQL with clauses such as FROM, WHERE, GROUP BY, ORDER BY, LIMIT etc ?

回答1:

Vertica's create function syntax prohibits the use of certain clauses in the expression.

Create function

CREATE [ OR REPLACE ] FUNCTION
... [[db-name.]schema.]function-name ( [ argname argtype  [, ...] ] )
... RETURN rettype
... AS 
... BEGIN
...... RETURN expression;
... END;

Note: Only one RETURN expression is allowed in the CREATE FUNCTION definition. FROM, WHERE, GROUP BY, ORDER BY, LIMIT, aggregation, analytics and meta function are not allowed.

To get around that you can use a procedure instead. Procedures in Vertica are not comparable to stored-procedures / PL-SQL (Vertica does not support them). They are installed scripts written in another language (such as Bash). They take the syntax...

Create Procedure

CREATE PROCEDURE [[db-name.]schema.]procedure-name ( 
... [ argname ] [ argtype [,...] ] )
... AS 'exec-name'
... LANGUAGE 'language-name'
... USER 'OS-user'

You can configure a procedure to call a vsql client using bash. The following script does that. Your script can also take parameters passed by Vertica.

Bash procedure script

#!/bin/bash
/opt/vertica/bin/vsql --command 'select count(*) from my_table where condition > value;' -w 'XXX' --echo-all -h host db_name user_name
exit 0

Install the script using the admintool GUI or the command line

Install external script

The script must have the right owner and the setuid flag must be set. You can do that using chmod.

$ admintools -t install_procedure -d vmartdb -f /scratch/helloworld.sh -p ownerpassword
Installing external procedure...
External procedure installed

Create the script in the database and then call it

CREATE PROCEDURE my_proc_name() AS 'my_script.sh' LANGUAGE 'external' USER 'db_user';
select my_proc_name();

回答2:

Vertica does not support Stored Procedures as you have in some DBs. Instead it has User Defined Functions. You write them in Java or whatever but it runs INSIDE the database as if it were an SP. (it also supported External Procedures to run stuff outside the DB if you what you want)