Create Procedure

Usage

CREATE [OR REPLACE] VIRTUAL PROCEDURE views.<procedurename>( ... ) [ RETURNS ( ... ) ] [ OWNER <owner_name>] [EXECUTE AS <executor> ] 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 CData Virtuality Server;
• An existing procedure will be replaced if CREATE OR REPLACE is used. An existing procedure will not be replaced if the procedure definition (including formatting and comments) has not changed;
• OWNER means the owner of the procedure. Default value: current user;
• EXECUTE AS means the executor of the procedure. Allowed values: CALLER and OWNER. Default value: CALLER.

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 CData 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) OWNER 'admin' EXECUTE AS OWNER
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 the state:

OWNER and EXECUTE AS are available since v4.1