What's the syntax for creating a stored procedure with variables, temp table, while loop, etc.? Sample, how to change this SQL procedure to Vertica procedure? create procedure myprocedure (@id int) as select * from tblname where id = @id
Hello Ashley, For now, Vertica does not support a SQL procedure, its the Vertica external Procedure. So we cannot use SQL for creating procedures. The language supported for creating procedure is shell scripting . An external procedure is a procedure external to Vertica that you create, maintain, and store on the server. External procedures are simply executable files such as shell scripts, compiled code, code interpreters, and so on. So you need to create a .sh file first and put the functionality of procedure in the .sh, so variables, loops everything according to shell scripting. for more information on this refer the Programming guide by Vertica. Hope this helps.
A result set cannot be displayed when you run the Vertica external Procedure, but if its about returning one row, that can be done using Vertica variables Steps 1. Write your sh script and manipulate all records according to your requirements 2. At the end call Vertica terminal with variable set using the -v parameter and the variable and it value as the unix variable. 3. then in Terminal you can issue a \echo or Select statement with a variable to display the records. You need to redirect all the output to some file in order to see the result set.
For example, I've a table which stores user ids, and login dates; one record for every login. If I want to find number of users who have login for X number of consecutive days, I can easily write a SQL sproc to do that. How can I do that in Vertica?
Distinct days per id(id=2 has gap: no 2013-09-23, so seq braked) daniel=> select id, date(login_dt) from ashley group by id, date(login_dt) order by id, date(login_dt); id | date ----+------------ 1 | 2013-09-22 1 | 2013-09-23 1 | 2013-09-24 2 | 2013-09-22 2 | 2013-09-24 2 | 2013-09-25 (6 rows) Create sequence daniel=> select id, date, 1 + CONDITIONAL_TRUE_EVENT(date - lg = 1) over (partition by id order by date) seq from (select id, date(login_dt), lag(date(login_dt)) over (partition by id order by date(login_dt)) lg from ashley group by id, date(login_dt) order by id, date(login_dt)) foo; id | date | seq ----+------------+----- 1 | 2013-09-22 | 1 1 | 2013-09-23 | 2 1 | 2013-09-24 | 3 2 | 2013-09-22 | 1 2 | 2013-09-24 | 1 2 | 2013-09-25 | 2 (6 rows)
Comments
create table ashley(id int, login_dt timestamp);data: Distinct days per id (id=2 has gap: no 2013-09-23, so seq braked) daniel=> select id, date(login_dt) from ashley group by id, date(login_dt) order by id, date(login_dt); id | date ----+------------ 1 | 2013-09-22 1 | 2013-09-23 1 | 2013-09-24 2 | 2013-09-22 2 | 2013-09-24 2 | 2013-09-25 (6 rows) Create sequence daniel=> select id, date, 1 + CONDITIONAL_TRUE_EVENT(date - lg = 1) over (partition by id order by date) seq from (select id, date(login_dt), lag(date(login_dt)) over (partition by id order by date(login_dt)) lg from ashley group by id, date(login_dt) order by id, date(login_dt)) foo; id | date | seq ----+------------+----- 1 | 2013-09-22 | 1 1 | 2013-09-23 | 2 1 | 2013-09-24 | 3 2 | 2013-09-22 | 1 2 | 2013-09-24 | 1 2 | 2013-09-25 | 2 (6 rows)