IBM PureData System for Analytics, Version 7.1

The CREATE [OR REPLACE] PROCEDURE commands

Use the CREATE PROCEDURE command to create a stored procedure. Use CREATE OR REPLACE PROCEDURE to create a stored procedure or to update an existing procedure with a new return type, execution permissions, or procedure body.

Synopsis

CREATE [OR REPLACE] PROCEDURE <name> (<arguments>)
RETURNS <type> [ [ EXECUTE AS OWNER | EXECUTE AS CALLER ] ] LANGUAGE
NZPLSQL AS <procedure_body>;

Inputs

The CREATE OR REPLACE PROCEDURE command takes the following inputs:

Table 1. CREATE OR REPLACE PROCEDURE inputs
Input	Description
name	The name of the stored procedure that you want to create or replace. This name is the SQL identifier that is used to start the procedure in a SQL expression. If the stored procedure exists, you cannot change the name with the CREATE OR REPLACE command. For systems that support multiple schemas, you can specify a name in the format schema.procedure to create a procedure in a different schema of the current database. You cannot create a procedure in a different database.
arguments	Specifies a list of fully specified argument data types. You can also specify the VARARGS value to create a variable argument procedure where users can input up to 64 values of any supported data type. VARARGS is a mutually exclusive value; you cannot specify any other arguments in the list. If the stored procedure exists, you cannot change the argument type list with the CREATE OR REPLACE command. You can change some aspects of the argument types; for example, you can change the size of a string or the precision and scale of a numeric value. You can also remove VARARGS from the argument list, or add it to an otherwise empty argument list.
RETURNS <`type`>	Specifies the type of data returned by the procedure. The <`type`> value can be a IBM® Netezza® data type or the value REFTABLE (<`table-name`>) to indicate that it returns a result set that looks like the specified table. The table must exist, although it can be empty, and it continues to exist after the procedure.
EXECUTE AS OWNER	If specified, the stored procedure runs by using the procedure owner ID for all access control and permission checks. This is the default.
EXECUTE AS CALLER	If specified, the stored procedure runs by using the ID of the user who called the procedure for all access control and permission checks.
LANGUAGE	Specifies the programming language used for the procedure. The default and only supported value is NZPLSQL.
procedure_body	Specifies the text of the procedure and must be enclosed with single quotation marks or a BEGIN_PROC/END_PROC pair. You can obfuscate the body to mask the content from users who have permission to show the procedure.

Outputs

The CREATE [OR REPLACE] PROCEDURE command has the following outputs:

Table 2. CREATE [OR REPLACE] PROCEDURE outputs
Output	Description
CREATE PROCEDURE	The message that the system returns if the command is successful.
ERROR: creating procedure: permission denied.	The message indicates that the user does not have Create Procedure permission.
ERROR: User 'username' is not allowed to create/drop procedures.	The system returns this message if your user account does not have permission to create a stored procedure.
ERROR: Synonym 'name' already exists	The system returns this message if a synonym exists with the name that you specified for the stored procedure.
ERROR: ProcedureCreate: procedure `NAME` already exists with the same signature	This error is returned when you issue a CREATE PROCEDURE command and a stored procedure with the same name and argument type list exists in the database. Use CREATE OR REPLACE PROCEDURE instead.
NOTICE: FunctionCreate: existing UDX `NAME`(ARGS) differs in size of string/numeric arguments	This message indicates that a stored procedure exists with the name but has different sizes specified for string or numeric arguments. If you did not intend to change the stored procedure signature, check the signature and ensure that it is correct.
ERROR: Can't specify arguments to a varargs procedure	You cannot specify both the VARARGS value and any other argument value in the arguments list. The VARARGS value is mutually exclusive.

Description

When you create a stored procedure, the signature of the stored procedure (that is, its name and argument type list) must be unique within its database. No other stored procedure can have the same name and argument type list in the same database.

You cannot change the stored procedure name or the argument type list with the CREATE OR REPLACE command. You can change some aspects of the argument types; for example, you can change the size of a string or the precision and scale of a numeric value, and you can add or remove the VARARGS value in an otherwise empty argument list. To change name or argument type list of a stored procedure, you must drop the stored procedure and then create a stored procedure with the new name or argument type list.

You cannot replace a stored procedure that is currently in use in an active query. After the transaction completes for an active query, the Netezza system processes the CREATE OR REPLACE PROCEDURE command.

Privileges required: You must have Create Procedure permission to use the CREATE PROCEDURE command. Also, if you use CREATE OR REPLACE PROCEDURE to change a stored procedure, you must have Create Procedure and Alter permission to change it.
Common tasks: Use the CREATE PROCEDURE command to create and become the owner of a new stored procedure. You can use the ALTER PROCEDURE command to change the owner of a procedure.

Usage

To create a procedure called customername:

   MYDB.SCHEMA(USER)=> CREATE OR REPLACE PROCEDURE customer() RETURNS INT8
LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN RAISE NOTICE 'The customer
name is alpha'; END; END_PROC;

To create a new procedure called customername in a different schema of the same database:

MYDB.SCHEMA(USER)=> CREATE OR REPLACE PROCEDURE sch_two.customer()
RETURNS INT8 LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN RAISE NOTICE 'The
customer name is alpha'; END; END_PROC;