Create Procedure

You are looking at an older version of the documentation. The latest version is found here.

Usage

CREATE VIRTUAL PROCEDURE views.<procedurename>( ... ) [ RETURNS ( ... ) ] AS BEGIN <procedure code> END 

• Use the views schema to create the procedures;
• The keyword VIRTUAL denotes that the procedure does not depend on the source system and will be evaluated by Data Virtuality Server.

Syntax Rules

• All virtual procedures should be created only in virtual schemas, such as views. Once created, they will appear in the virtual schemas section in the Data Virtuality Studio;
• Virtual procedures cannot be created in system schemas (SYS, SYSADMIN, UTILS, pg_catalog, and the analytical storage schema).

Please refer to Virtual Procedures and Procedural Language and Virtual Procedures for more information.

Example

CREATE VIRTUAL PROCEDURE views.dateaxis(IN startdate date,IN enddate date) RETURNS (xdate date)
AS 
BEGIN
DECLARE date idate;
idate=startdate;
CREATE LOCAL TEMPORARY TABLE #x(xdate date);
  WHILE (idate<=enddate)
  BEGIN
	INSERT INTO #x(xdate) VALUES (idate);
	idate=timestampadd(SQL_TSI_DAY,1,idate);
  END
SELECT * from #x;
END;;

Use double semicolon ;; as a statement separator. It is necessary to distinguish statements inside and outside the procedure block.

State

Once a procedure has been created, it will have a state: