Netezza Stored Procedures Guide

IBM Netezza 6.0.
3 and Later
IBM Netezza Stored Procedures Developers Guide

Revised: September 26, 2011
20470-03 Rev. 2
Note: Before using this information and the product that it supports, read the information in Notices and Trademarks on page C-1.
Copyright IBM Corporation 2009, 2011. US Government Users Restricted Rights Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
Contents
Preface 1 Introduction to Stored Procedures
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-1 NZPLSQL Language. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2 SQL Commands for Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2 Stored Procedures Input and Return Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-3 How to Create and Use a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-3 How to Execute a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-3 Best Practices for Developers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4 Security Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4 Cross-Database Access to Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4 Block Quoting Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-5 SQL Restrictions within the Stored Procedure. . . . . . . . . . . . . . . . . . . . . . . . . . . 1-6 Best Practices for Netezza Administrators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-6 Backing Up and Restoring Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-7 Upgrading and Patching Netezza Systems that Have Stored Procedures . . . . . . . . 1-7 Downgrading Netezza Systems with Stored Procedures . . . . . . . . . . . . . . . . . . . . 1-7
2 NZPLSQL Statements and Grammar

NZPLSQL Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-1 Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2 Variables and Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2 Parameter Passing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-4 Argument List and Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-4 Variable Scoping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-5 Data Types and Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6 Array Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-9 Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-9 Literals and Math Operations in Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . 2-10 Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-14 Assignment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-14 Calling Another Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-14 Executing Dynamic Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-15
iii
Obtaining Other Results Status . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-15 Returning From a Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-16 Control Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-16 Conditional Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-16 Iterative Control. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-17 Working with Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-19 Declaration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-19 Assignments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-19 Iterating Through Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-20 Aborting and Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-21 Exceptions and Error Messages Support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-22 Returning a Result Set. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-23 Managing Large Datasets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-25 Advanced Development Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-25 Extending the Language with UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-25 Tips and Developer Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-26
3 Creating and Managing Stored Procedures

Managing User Account Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-1 Granting Create Permission . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2 Granting All Object Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2 Revoking Create Permission . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2 Managing Alter Permission . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2 Managing Execute Permission. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2 Managing Drop Permission . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3 Creating a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3 Design a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3 Create a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3 Understanding Size-Specific, Generic, and Variable Argument Procedures . . . . . . 3-5 Obfuscating the Procedure Body . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-7 Calling or Invoking a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-10 Altering a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-10 Commenting on a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-11 Dropping a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-11 Showing Information About a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-12 NzAdmin UI for Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-12
iv
Appendix A: SQL Reference

ALTER PROCEDURE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-1 Synopsis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-1 Inputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-2 Outputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-2 Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-3 Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-4 CALL and EXEC[UTE [PROCEDURE]] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-4 Synopsis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-4 Inputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-4 Outputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-4 Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-5 Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-5 CREATE [OR REPLACE] PROCEDURE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-6 Synopsis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-6 Inputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-6 Outputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-7 Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-8 Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-8 DROP PROCEDURE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-9 Synopsis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-9 Inputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-9 Outputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-9 Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-10 Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-10 SHOW PROCEDURE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-10 Synopsis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-10 Inputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-11 Outputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-11 Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-11 Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-12
Appendix B: Stored Procedure Examples

Variable Argument Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . B-1 Example of Simulating an Anonymous Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . B-2
Appendix C: Notices and Trademarks

Notices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C-1
Trademarks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C-3 Open Source Notifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C-4 Electronic Emission Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C-6 Regulatory and Compliance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C-9
Index
vi
List of Tables
Table 2-1: Table A-1: Table A-2: Table A-3: Table A-4: Table A-5: Table A-6: Table A-7: Table A-8: Table A-9: Table A-10: Table A-11: Supported Data Types for Variables . . . . . . . . . . . . . . . . . . . . . . . . . 2-7 Stored Procedure SQL Commands. . . . . . . . . . . . . . . . . . . . . . . . . . A-1 ALTER PROCEDURE Input. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-2 ALTER PROCEDURE Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-2 CALL and EXEC[UTE [PROCEDURE]] Input . . . . . . . . . . . . . . . . . . . A-4 CALL and EXEC[UTE [PROCEDURE]] Output . . . . . . . . . . . . . . . . . . A-4 CREATE OR REPLACE PROCEDURE Input . . . . . . . . . . . . . . . . . . . A-6 CREATE [OR REPLACE] PROCEDURE Output . . . . . . . . . . . . . . . . . A-7 DROP PROCEDURE Input . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-9 DROP PROCEDURE Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-9 SHOW PROCEDURE Input . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-11 SHOW PROCEDURE Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-11
vii
viii
Preface
The IBM Netezza Stored Procedures Developers Guide describes how to create and use stored procedures on a Netezza data warehouse appliance.
About This Guide

This guide is written for IBM Netezza users who plan to create and use stored procedures. The guide contains the following topics: Topics See
An overview of stored procedures, their use, Introduction to Stored Procedures on and how to develop them for a Netezza system page 1-1 A description of the NZPLSQL language statements How to create, manage, and drop stored procedures using SQL commands and the NzAdmin interface A reference of the new Netezza SQL commands for creating and managing stored procedures Examples of stored procedures NZPLSQL Statements and Grammar on page 2-1 Creating and Managing Stored Procedures on page 3-1 SQL Reference on page A-1
Stored Procedure Examples on page B-1
The Purpose of This Guide

This guide describes how to create stored procedures on an IBM Netezza system using the Netezza procedural language, NZPLSQL. It provides an overview of the language and statements, and how to create, alter, and drop procedures using Netezza SQL commands. To use this guide, you should be very familiar with the Netezza systems and architecture, as well as Netezza SQL commands and how to access a Netezza database. You should also be familiar with the general concepts of stored procedures, how to create structured language programs, and how to debug applications.
Symbols and Conventions

This guide uses the following typographical conventions:

Italics for terms, and user-defined variables such as file names Upper case for SQL commands; for example INSERT, DELETE Bold for command line input; for example, nzsystem stop
ix
If You Need Help

If you are having trouble using the Netezza appliance, you should: 1. Retry the action, carefully following the instructions given for that task in the documentation. 2. Go to the Netezza Knowledge Base at https://knowledge.netezza.com. Enter your support username and password. You can search the knowledge base or the latest updates to the product documentation. Click Netezza HelpDesk to submit a support request. 3. If you are unable to access the Netezza Knowledge Base, you can also contact Netezza Support at the following telephone numbers:

North American Toll-Free: +1.877.810.4441 United Kingdom Free-Phone: +0.800.032.8382 International Direct: +1.508.620.2281
Refer to your Netezza maintenance agreement for details about your support plan choices and coverage.
Comments on the Documentation

We welcome any questions, comments, or suggestions that you have for the IBM Netezza documentation. Please send us an e-mail message at netezza-doc@wwpdl.vnet.ibm.com and include the following information:

The name and version of the manual that you are using Any comments that you have about the manual Your name, address, and phone number
We appreciate your comments on the documentation.
CHAPTER 1
Introduction to Stored Procedures
Whats in this chapter
Stored Procedures How to Create and Use a Stored Procedure How to Execute a Stored Procedure Best Practices for Developers Best Practices for Netezza Administrators
This chapter provides an overview of the support for stored procedures in the IBM Netezza family of data warehouse appliances. Stored procedure support is available in Netezza Release 4.6 and later. This guide describes changes available in Release 6.0.x and later.
Stored Procedures
Netezza stored procedures combine the benefits of SQL to query and manipulate database information with the benefits of a procedural programming language to handle data processing, transaction logic, and application branching behaviors. For example, if you have a database that contains customer information, inventory, and sales records, you might also have an application that processes the sale of an item in inventory. When an order request arrives, the application might be designed to query the database to determine how many items of that type are available in inventory, and then to take actions such as the following:
If the available inventory is less than the order number, the application processes the request for the available number and notifies an order administrator to order more inventory to complete the purchase. If the available inventory is greater than the order request, the application processes the order and updates the database to show the reduction in the current inventory. If the inventory is discontinued, the application saves the order request and returns a message that the item is no longer available. It may also query for related or replacement inventory to report alternative merchandise.
While such an application could be managed with a third-party business application that accesses the Netezza database to perform these tasks, you can also use Netezza stored procedures to encapsulate this application as an object in the Netezza database. SQL provides the power to access and update the database information on the host, and the procedure
1-1
language provides the logic for if-then-else branching and application processing. Because the application resides on the Netezza host, the application performance can benefit from its location onsite by avoiding the network time between an application client system and the Netezza host. The application itself also becomes easier to maintain, as it resides in only one location (the Netezza host) and thus versioning and updates need only be made in one place to keep the application up-to-date for all users. With Netezza stored procedures, you can also take advantage of security and access benefits. Stored procedures can be defined to run with the credentials of the user who created the procedure or the credentials of the user who is running the procedure. The procedure manages the access to information contained in various base tables and views. You can grant a user permission to run a stored procedure without granting that user explicit access to underlying tables or views.
NZPLSQL Language
You implement stored procedures on the Netezza host by creating applications using the NZPLSQL language. NZPLSQL is an interpreted language which is based on Postgres PL/pgSQL language and designed for the Netezza host environment. NZPLSQL is a scripting language embedded in SQL. As a procedural language, it has branch, loop, and subprogram structures while SQL provides the main program. The subprograms, known as procedures, can take arguments and declare internal variables. Once stored in a database, these procedures can be called from within other databases on the same Netezza host. The NZPLSQL language provides for the following types of procedural logic:

Conditionals (if/else) Looping (while, for) Execution of SQL (including dynamic SQL) Variables Returning a scalar result or a result set Input arguments Execution in the calling context (session and transaction) Extending functionality (adding NZPLSQL library routines)
SQL Commands for Stored Procedures

To support stored procedures in the Netezza database, the Netezza SQL language has been extended to include new commands for stored procedures:

ALTER PROCEDURE CALL or EXEC[UTE[ PROCEDURE]] CREATE [OR REPLACE] PROCEDURE DROP PROCEDURE SHOW PROCEDURE
1-2
20470-03
Rev.2
How to Create and Use a Stored Procedure
You can use any SQL tool that supports ODBC, JDBC, OLE-DB to enter these commands. This document contains examples that use the nzsql command line tool. Appendix A, SQL Reference, describes these commands and their syntax in detail. In addition to these procedure-specific commands, you can also use the commands GRANT and REVOKE to permit or deny access to procedures, as well as COMMENT ON to add descriptions and details to the procedure definitions. For more information about managing procedures, refer to Chapter 3, Creating and Managing Stored Procedures.
Stored Procedures Input and Return Types

Netezza stored procedures are a hybrid between functions and procedures (as defined by the SQL:2003 standard for SQL-invoked routines). In the Netezza implementation, an NZPLSQL stored procedure takes only input arguments and supports a return value as functions do. Unlike functions, a stored procedure must be called using CALL or a similar construct, and it is not allowed in the usual query locations where a built-in or standard function is allowed. Note: A stored procedure can also be designed to return a result set. For more information, see Returning a Result Set on page 2-23.
How to Create and Use a Stored Procedure

As a high-level summary, follow these steps to create and use a stored procedure. These steps are described in more detail in Chapter 3, Creating and Managing Stored Procedures. 1. Design the stored procedure logic and operation. 2. Define the stored procedure object using the CREATE [OR REPLACE] PROCEDURE command. 3. Run or execute the procedure from the SQL command prompt. As with other Netezza objects, the Netezza admin user can create, list, and execute stored procedures by default. The admin user may also grant other users permission to create, list, and execute stored procedures on the Netezza system. For more information about permissions, see Managing User Account Permissions on page 3-1.
How to Execute a Stored Procedure

You execute a stored procedure in the SQL command using either the CALL or EXEC[UTE[ PROCEDURE]] commands. For example, if you have a stored procedure named updateacct(), you can run it using any of the following commands:
MYDB(USER)=> MYDB(USER)=> MYDB(USER)=> MYDB(USER)=> CALL updateacct(); EXEC updateacct(); EXECUTE updateacct(); EXECUTE PROCEDURE updateacct();
You can also use the SELECT command to execute a procedure; however, you cannot specify a FROM clause. For example:
MYDB(USER)=> SELECT updateacct();
20470-03
Rev.2
1-3
To execute the procedure, the user must be the owner of or have permission to execute the updateacct() procedure. For more information about the CALL and EXEC[UTE[ PROCEDURE]] commands, refer to Appendix A, SQL Reference.
Best Practices for Developers

The following sections describe some best practices for users who develop stored procedures.
Security Considerations
When you define a stored procedure, you can specify whether the Netezza system should execute the procedure using the ID of the owner who created the stored procedure or the ID of the user who runs the procedure. This allows you an additional layer of security (or expanded access) for the data processed by the stored procedure. For example, if the admin user created the stored procedure and specified execute as owner permissions, which is the default, then any other user who is permitted to execute the procedure will run the procedure as the admin user. This could, for example, allow the user to see results from tables or views that he or she does not have permission to access using SQL directly. If the procedure should access only the data that the user is permitted to see, the stored procedure should be defined as execute as caller. In this case, the procedure uses the user ID of the calling user, and thus the access permissions of that user.
Cross-Database Access to Stored Procedures

When you create a stored procedure using the CREATE [OR REPLACE] PROCEDURE command, the command adds the procedure to the database to which you are connected. You can define the stored procedure in more than one database if necessary. Typically, most users are logged in to the database that contains the stored procedure that they plan to run. However, the Netezza system allows for cross-database access of stored procedures using two methods:
Using fully-qualified object names when calling a procedure object that resides within a different database, for example:
MYDB(ADMIN)=> EXEC OTHERDB..UPDATEACCT();
Using the PATH SQL session variable to specify the databases to search to find a procedure. To use the PATH session variable, you enter a command similar to the following:
MYDB(ADMIN)=> SET PATH = <elem> [, <elem>];
The <elem> value can be a database name or the variables CURRENT_CATALOG, CURRENT_USER, CURRENT_SCHEMA or CURRENT_PATH. Anything you specify as <elem> must resolve to a database name. For example:
MYDB(ADMIN)=> SET PATH = mydb, nzdb, customer; SET VARIABLE
To display the PATH value, use the following command:
1-4
20470-03
Rev.2
Best Practices for Developers
MYDB(ADMIN)=> SELECT CURRENT_PATH; CURRENT_PATH -----------------MYDB,NZDB,CUSTOMER (1 row)
The Netezza system uses the PATH variable during the lookup of any unqualified procedures. It searches the current database if PATH is not set; otherwise it searches the databases specified in PATH, in the order that they are specified. The Netezza system uses the first match that it finds, even if a better match might exist in a subsequent database. A poorer match is one that might require implicit casting of arguments or that causes an error due to multiple potential matches. Note that PATH searches databases, not schemas, as there is no schema support for this capability. Also, the Netezza system uses the PATH session variable to find only stored procedures, user-defined functions (UDFs), and userdefined aggregates (UDAs). Other object types are not supported. It is important to note that if a stored procedure is invoked from a different database, unqualified objects will bind to objects in the invoking database, not in the database where the stored procedure resides. Note that unqualified stored procedures, UDFs, and UDAs are exceptions to this rule because the Netezza system first uses the PATH variable to search for those three object types before it searches within the invoking database. If you plan to invoke cross-database calls to stored procedures that access data in other databases, make sure that the stored procedure uses fully-qualified object names to refer to those data objects. Note: Certain types of operations will not work across databases, such as insert, update, and delete operations. For any procedure which contains those types of operations, make sure that you are connected to the database where the stored procedure is defined.
Block Quoting Support

Since stored procedures are defined as a block or body of numerous lines of text, Netezza provides a block-quoting mechanism to help ease the definition of the procedure body as well as to make the content more readable for debugging and learning. A section of text bounded by BEGIN_PROC and END_PROC is a block quote. An example follows:
CREATE OR REPLACE PROCEDURE name() RETURNS INT4 LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE string varchar; BEGIN string := 'This string is quoted'; END; END_PROC;
As shown in the example, single quotes are not escaped within the block-quoted string. The string content is always written literally. Backslashes have no special escape meaning. Note: The stored procedure body is in clear text format by default. Permitted users can use the SHOW PROCEDURE VERBOSE command and interfaces such as NzAdmin to review the stored procedure. If necessary, you can hide the procedure code when you create the procedure. For more information, see Obfuscating the Procedure Body on page 3-7. Block quotes are intended for use only in NZPLSQL body quoting or stored procedure invocation. The execution commands CALL and EXEC[UTE[ PROCEDURE]] support them, although SELECT does not. They can be used inside a stored procedure body to build DDL
20470-03
Rev.2
1-5
statements where they are allowed, but they have no quoting implications inside the body. If they are used inside the body, make sure that there are an equal number of both keywords and that they match (a BEGIN_PROC appears before the corresponding END_PROC) to avoid errors. If your program logic dictates that they are not matching, they must be broken up (that is, 'BEGIN_' || 'PROC'). Both BEGIN_PROC and END_PROC can appear inside a single or double-quoted string in normal SQL statements, as long as the SQL statements are not inside a block quote. For example:
select "BEGIN_PROC" from <table>; insert into <table> values ('BEGIN_PROC');
If you want to do this inside a block quote body, then you must have a matching END_PROC. For example:
CREATE OR REPLACE PROCEDURE name() RETURNS INT4 LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE string varchar; BEGIN string := 'This string is quoted'; -- This comment is the match for below BEGIN_PROC insert into va values ('END_PROC'); END; END_PROC;
Because nested BEGIN_PROC and END_PROC keywords have no quoting implications, the following example is not supported:
CREATE OR REPLACE PROCEDURE name() RETURNS INT4 LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE string varchar; BEGIN -- The next statement is a syntax error since it is not quoted string := BEGIN_PROC This string is not quoted END_PROC; END; END_PROC;
SQL Restrictions within the Stored Procedure

Within the body of a stored procedure, you can use most SQL commands to perform tasks relating to database objects. However, you cannot use SQL commands that are prohibited for use within a BEGIN/COMMIT transaction block. For a description of the transaction control practices and command restrictions, see the IBM Netezza Database Users Guide.
Best Practices for Netezza Administrators

The following sections describe some best practices for Netezza administrators who manage Netezza systems that have stored procedures.
1-6
20470-03
Rev.2
Best Practices for Netezza Administrators
Backing Up and Restoring Stored Procedures

Any stored procedures that you create are backed up and restored by the Netezza backup and restore operations. If you backup and restore obfuscated procedures, those restored procedures remain obfuscated. If you restore a database to a system that is running a release which does not support obfuscated procedures or VARARGS, for example, those procedures will not run and the queries that use them will return a syntax error. If you drop a database, note that all the objects defined in the database including stored procedures are likewise dropped. As a best practice, you should keep backup copies of your source CREATE OR REPLACE PROCEDURE definitions in a safe location outside of the Netezza system. Make sure that you have recent backups of your Netezza systems in the event that you need to recover from an accidental deletion or change, or to restore Netezza services as part of a disaster recovery situation.
Upgrading and Patching Netezza Systems that Have Stored Procedures

There are no special requirements or procedures necessary to preserve any stored procedures during a service pack update or an upgrade to a new release. The stored procedures should continue to operate in the same manner on the newly updated or upgraded system as on the previous release. If you downgrade the system, note that your stored procedures should continue to run if the downgrade release also supports stored procedures. However, if you downgrade from a Release 6.0.x version to a previous release such as 5.0.x which does not support obfuscated procedures or VARARGS, for example, those procedures will not run and the queries that use them will return a syntax error. If the new release or service pack introduces any new features or changes that could affect the operation of stored procedures, Netezza will describe the changes in the release notes for the service pack or release. Before you install any new release or service pack, you should review the release notes to familiarize yourself with any new features, changes, fixes, and known issues for that release. You should also make sure that you have a recent backup of your Netezza system before you upgrade the system.
Downgrading Netezza Systems with Stored Procedures

If you downgrade your Netezza release, note that downgrades to any releases prior to Release 4.6 will drop all stored procedures automatically. If you downgrade to a release that supports stored procedures, the procedures should continue to work after the downgrade, unless your stored procedures use features that were available only in the later release.
20470-03
Rev.2
1-7
1-8
20470-03
Rev.2
CHAPTER 2
NZPLSQL Statements and Grammar
NZPLSQL Structure Comments Variables and Constants Array Variables Expressions Statements Control Structures Working with Records Aborting and Messages Exceptions and Error Messages Support Returning a Result Set Advanced Development Topics
This chapter describes the NZPLSQL language, its structure, and how to use the language to create stored procedures. NZPLSQL is very similar to the Postgres PL/pgSQL language. Some of the language descriptions in this chapter leverage the Postgres PL/pgSQL documentation. However, NZPLSQL has some differences which are based on the Netezza systems design and environment. NZPLSQL also includes some extensions to assist users who may be migrating stored procedures written in other languages to the Netezza environment.
NZPLSQL Structure
NZPLSQL is a block structured language. A block is defined as follows:
[<<label>>] [DECLARE declarations] BEGIN statements [EXCEPTION WHEN OTHERS THEN handler statements] END;
2-1
The statements section of a block can contain zero, one, or more sub-blocks. A sub-block is used for logical grouping or to localize variables to a small group of statements. All of the keywords and identifiers are case-insensitive and can be used in mixed upperand lower-case mode. Identifiers are automatically converted to uppercase, as they are in general SQL statements, unless they are enclosed in quotes to keep them case-sensitive. The variables declared in the declarations section preceding a block are initialized to their default values every time the block is entered, not just once per procedure call. Note: Be careful not to confuse the use of BEGIN/END for grouping statements in NZPLSQL with the BEGIN/END SQL database commands for transaction control. The NZPLSQL BEGIN/END keywords are used only for grouping; they do not start or end a transaction. Procedures are always executed within a transaction established by an outer querythey cannot start or commit transactions, since Netezza SQL does not have nested transactions.
Comments
There are two types of comments in NZPLSQL.
A double dash (--) starts a comment that extends to the end of the line. For example:
-- This is a comment line.
A forward-slash asterisk pair (/*) starts a block comment, which extends to the next occurrence of the ending sequence which is an asterisk forward-slash (*/) pair. For example:
/* This is a block comment, which can span multiple lines. Any code inside the block such as: url varchar := 'http://www.netezza.com' is ignored. */
Block comments cannot be nested, but double dash comments can be enclosed in a block comment. Note that a double dash can hide the block comment delimiters /* and */.
Variables and Constants

All of the variables, rows, and records that are used in a block or its sub-blocks must be declared in the declarations section of a block. (There are some exceptions, namely the loop variable of a FOR loop that iterates over a range of integer values, and some built-in variables like FOUND, ROW_COUNT, and LAST_OID.) NZPLSQL variables can have any SQL datatype, such as INTEGER, VARCHAR, and CHAR. Some sample variable declarations follow:
user_id INTEGER; quantity NUMERIC(5,2); url VARCHAR;
A variable declaration has the following syntax:

name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } value ];
2-2
20470-03
Rev.2
The DEFAULT clause, if included, specifies the initial value assigned to the variable when the block is entered. If a DEFAULT clause is not specified, the variable uses the SQL NULL value as its default. The CONSTANT option means that the variable cannot be changed; its value remains constant for the duration of the block. If NOT NULL is specified, an assignment of a NULL value results in a runtime error. Since the default value of all variables is the SQL NULL value, all variables declared as NOT NULL must also specify a non-null default value.
When specifying types in declarations, NUMERIC may be specified with or without a precision and scale. CHAR, NCHAR, VARCHAR, and NVARCHAR may be specified with or without a size. When these types are specified with a size or a precision/scale, assignment to the variable will follow normal cast rules. If they are specified without sizes, assignment will preserve the original source size or precision/scale. The default value is evaluated each time the procedure is called. So assigning now() to a variable of type timestamp causes the variable to be set to the time of the actual procedure call, not the time when the procedure was precompiled into its bytecode. Some examples of variable assignments follow:
quantity INTEGER := 32; url varchar := 'http://mysite.com'; user_id CONSTANT INTEGER := 10;
Using the %TYPE and %ROWTYPE attributes, you can declare variables with the same datatype or structure of another database item (for example, a table field). %TYPE provides the datatype of a variable or database column. You can use this to declare variables that will hold database values. For example, if you have a column named user_id in your users table, you can declare a variable with the same datatype as user_id as follows:
user_id users.user_id%TYPE;
By using %TYPE, you do not have to know the datatype of the structure that you are referencing. Also, if the datatype of the referenced item changes in the future (for example, you change your table definition of user_id to become a REAL), you do not have to change your procedure definition. You can declare a row with the structure of a given table, as follows:
name table%ROWTYPE;
The table value must be an existing table or view name of the database. The fields of the row are accessed using the dot notation. Only the user attributes of a table row are accessible in the row. You cannot access an OID or other system attribute because the row could be from a view. The fields of the rowtype inherit the table's sizes or precision for CHAR, NCHAR, and NUMERIC data types, as applicable.
20470-03
Rev.2
2-3
Parameter Passing
In NZPLSQL, parameters are not named. Instead, only the data types are passed, and parameters are referenced via their position. To name parameters, use the ALIAS FOR syntax. For example:
CREATE OR REPLACE PROCEDURE p1 (int, varchar(ANY)) RETURNS int LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE pId ALIAS FOR $1; pName ALIAS FOR $2; BEGIN INSERT INTO t1 SELECT * FROM t2 WHERE id = pId;
The example highlights the recommended convention to name parameters with a p prefix to differentiate them from variables. This convention is helpful because parameters are constant and cannot be modified. If a parameter must be updated (for example, to assign it a default value if it is null), assign the parameter to a variable and update the variable. For example:
DECLARE pId ALIAS FOR $1; vID integer; BEGIN vId := ISNULL(pId, 0); INSERT INTO t1 SELECT * FROM t2 WHERE id = vId;
Argument List and Variables

The arguments passed as input to procedures can be identified using the names $1 (first argument), $2 (second argument), and so on. You can also use the $var notation, which takes the integer value of var to identify the input value specified in that order position. You can specify from 0 to 64 arguments. If you specify VARARGS in the input argument list, users can input any number of arguments up to a maximum of 64. Netezza saves the OIDs of the input arguments in the PROC_ARGUMENT_TYPES array. The array has the same size as the number of parameters that are passed to the stored procedure. Its elements are of type OID and contain the OID type of the corresponding input argument. You can use the array to obtain the number and type of each argument. For example:
CREATE OR REPLACE PROCEDURE test(VARARGS) RETURNS INT4 LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE num_args int4; typ oid; idx int4; BEGIN num_args := PROC_ARGUMENT_TYPES.count; RAISE NOTICE 'Number of arguments: %', num_args; for i IN 0 .. PROC_ARGUMENT_TYPES.count - 1 LOOP typ := PROC_ARGUMENT_TYPES(i); idx := i+1;
2-4
20470-03
Rev.2
RAISE NOTICE 'argument $% is type % and has the value ''%''', idx, typ, $idx; END LOOP; END; END_PROC;
A sample call follows:

MYDB(USR2)=> CALL TEST (1, 3, 9::bigint, 'test value', 324.56); NOTICE: Number of arguments: 5 NOTICE: argument $1 is type 23 and has the value '1' NOTICE: argument $2 is type 23 and has the value '3' NOTICE: argument $3 is type 20 and has the value '9' NOTICE: argument $4 is type 705 and has the value 'test value' NOTICE: argument $5 is type 1700 and has the value '324.56' test -----(1 row)
Variable Scoping
When you explicitly declare a new variable (or name), the scope of the variable is the block in which it is defined. For example, the following sample defines a variable named val in the <<outer>> blocks DECLARE section, and then defines another val variable in the <<inner>> blocks DECLARE section. Although the variables have the same name, these are two different variables:
<<outer>> DECLARE val int4; BEGIN val := 5; <<inner>> DECLARE val int4; BEGIN val := 7; RAISE NOTICE 'inner val != outer val % %', val, outer.val; END; RAISE NOTICE 'outer val is 5 %', val; END;
In this example, note that the block labels inner and outer can be used to identify a specific val variable using its fully-qualified variable name. If you create this as a stored procedure and run it, you would see output similar to the following:
MYDB(USER)=> call vals(); NOTICE: inner val != outer val 7 5 NOTICE: outer val is 5 5 VALS -----(1 row)
When you declare variables for loop iterators, which are described in Loop Statement on page 2-17, note that the iterator variables have their own unique scope. For example, you could use a variable named val as a loop iterator. Using the same sample procedure, this would give you three unique val variables in your procedure, as follows:
20470-03
Rev.2
2-5
<<outer>> DECLARE val int4; BEGIN val := 5; <<inner>> DECLARE val int4; BEGIN val := 7; RAISE NOTICE 'inner val != outer val % %', val, outer.val; FOR val IN 1..10 LOOP --Note that this is a NEW val variable for the loop. RAISE NOTICE 'The value of val is %', val; END LOOP; RAISE NOTICE 'inner val is still 7. Value %', inner.val; END; RAISE NOTICE 'outer val is still 5. Value %', val; END;
Sample output for this stored procedure follows:

MYDB(USER)=> call vals(); NOTICE: inner val != outer val 7 5 NOTICE: The value of val is 1 NOTICE: The value of val is 2 NOTICE: The value of val is 3 NOTICE: The value of val is 4 NOTICE: The value of val is 5 NOTICE: The value of val is 6 NOTICE: The value of val is 7 NOTICE: The value of val is 8 NOTICE: The value of val is 9 NOTICE: The value of val is 10 NOTICE: inner val is still 7. Value 7 NOTICE: inner val is still 5. Value 5 VALS -----(1 row)
As shown in the output, the val variable of the loop iterator has its own scope. It has its own value and does not affect the other two val definitions. Within the loop, if you need to refer to a specific variable that is defined outside the for loop, you can use the fully qualified form of the variable name (label.variable).
Data Types and Aliases

Table 2-1 lists the supported data types by their preferred name form, and includes supported aliases and some notes about the values. For more information about the data types and values, see the IBM Netezza Database Users Guide.
2-6
20470-03
Rev.2
Table 2-1: Supported Data Types for Variables Data Type BOOLEAN Alias Name(s) BOOL Notes A boolean field can store true values, false values, and null. You can use the following words to specify booleans: true or false, on or off, 0 or 1, 'true or false, t or f, on or off, yes or no. Fixed length character string, blank padded to length n. If you do not specify n, the default is an unsized CHAR value. The maximum character string size is 64,000. Variable length character string to a maximum length of n. If you do not specify n, the default is an unsized VARCHAR value. There is no blank padding, and the value is stored as entered. The maximum character string size is 64,000. Fixed length character string, blank padded to length n. If you do not specify n, the default is an unsized NCHAR value. The maximum length is 16,000 characters. Variable length character string to a maximum length of n. If you do not specify n, the default is an unsized NVARCHAR value. The maximum length is 16,000 characters.
CHAR
CHARACTER, CHAR(n), CHARACTER(n) CHARACTER VARYING, VARCHAR(n), CHARACTER VARYING(n), CHAR VARYING(n) NATIONAL CHARACTER, NATIONAL CHAR(n), NCHAR(size) NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING(n), and NVARCHAR(n)
VARCHAR
NCHAR
NVARCHAR
DATE
Specifies a day with resolution that spans January 1, 0001 to December 31, 9999 (centered around 2000-01-01). DATETIME Has a date part and a time part, with seconds stored to 6 decimal positions. The value represents the number of microseconds since midnight 2000-01-01. Min: -63,082,281,600,000,000 (00:00:00, 1/1/ 0001) Max: 252,455,615,999,999,999 (23:59:59.999999, 12/31/9999)
TIMESTAMP
20470-03
Rev.2
2-7
Table 2-1: Supported Data Types for Variables (continued) Data Type TIME Alias Name(s) TIME WITHOUT TIME ZONE Notes Hours, minutes, and seconds to 6 decimal positions. Ranging from 00:00:00.000000 to 23:59:59.999999. This is microsecond resolution that represents the time of day only (midnight to one microsecond before midnight). An interval of time. It has microsecond resolution and ranges from +/- 178000000 years. The time part represents everything but months and years (microseconds) and the month part represents months and years. For more information see the section on Netezza interval support in the IBM Netezza Database Users Guide. Hours, minutes, seconds to 6 decimal positions, and time zone offset from GMT. Ranging from 00:00:00.000000+13:00 to 23:59:59.99999912:59. Fixed-point numeric types with precision p and scale s. Precision can range from 1 to 38, scale from 0 to the precision. NUMERIC(p) is equivalent to NUMERIC(p,0). NUMERIC is an unsized numeric value. Although decimal is sometimes a distinct SQL data type, Netezza SQL treats it as an alias for numeric. Floating point number with precision p. Precision values of 1 to 6 are equivalent to FLOAT(6), and are stored as a 4-byte value. Netezza SQL prefers the type name real, as float(p) is considered more of an alias for the preferred form. Floating point number with precision p, from 7 to 15. Precision values between 7 and 15 are equivalent to 15 and are stored as an 8-byte value. Netezza SQL prefers the type name double precision, as float(p) is considered more of an alias for the preferred form. 32-bit values in range 2,147,483,648 to 2,147,483,647 8-bit values in the range 128 to 127 16-bit values in range 32,768 to 32,767
INTERVAL
TIMESPAN
TIME WITH TIME ZONE
TIMETZ
NUMERIC(p, s)
NUMERIC, DEC, DEC(p,s), DECIMAL(p,s), DECIMAL
REAL
FLOAT(p), FLOAT4
DOUBLE PRECISION
DOUBLE, FLOAT, FLOAT(p), FLOAT8
INTEGER
INT, INT4
BYTEINT SMALLINT
INT1 INT2
2-8
20470-03
Rev.2
Array Variables
Table 2-1: Supported Data Types for Variables (continued) Data Type BIGINT Alias Name(s) INT8 Notes 64-bit values in range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Array Variables
In addition to normal scalar variables, NZPLSQL also supports array variables. To declare an array variable, use the following syntax:
name VARRAY(size) OF type;
All of the elements of the array will initially be set to the SQL NULL value for the declared type. To assign a value to an element, do the following:
name(idx) := value;
This syntax will raise an exception if the index (idx) is out of bounds. The following methods are supported:
name.EXTEND(size) name.COUNT name.TRIM(size)
The EXTEND method will extend the array by the specified size. If size is omitted, the default is 1. COUNT returns the number of elements in the array. TRIM deletes the last size elements in the array (the default is 1). TRIM and EXTEND will raise an exception if size is out of bounds. Array references are allowed almost anywhere variable references are allowed, with the following exceptions:

As an argument to RAISE As an INTO variable As the variable in a FOR LOOP As part of a default value
Expressions
All expressions used in NZPLSQL statements are processed using the backends executor. Expressions that appear to contain constants could require run-time evaluation (for example, using 'now()' for the timestamp type) so it is impossible for the NZPLSQL parser to identify real constant values other than the NULL keyword. All expressions are evaluated internally by executing a query such as the following:
SELECT expression
In the expression, occurrences of variable identifiers are substituted by parameters and the actual values from the variables are passed to the executor in the parameter array. All expressions used in a NZPLSQL procedure are compiled and cached the first time they are
20470-03
Rev.2
2-9
encountered. (Everything in NZPLSQL is cached, with the exception of variable values and SQL plans.) If there is a compile error (syntax error), the expression will not be cached. The cached copy is preserved until one of the following happens:

The procedure body is modified. The procedure is dropped. The database session ends.
The type checking done by the Netezza SQL main parser has some side effects to the interpretation of constant values. For example, the following two examples are different in how constant values are interpreted. The first example follows:
DECLARE logtxt ALIAS FOR $1; BEGIN INSERT INTO logtable VALUES (logtxt, 'now()'); RETURN 'now()'; END;
The second example follows:

DECLARE logtxt ALIAS FOR $1; curtime timestamp; BEGIN curtime := 'now()'; INSERT INTO logtable VALUES (logtxt, curtime); RETURN curtime; END
In the first example, when the Netezza SQL main parser prepares the plan for the INSERT, it interprets now() as a timestamp because the target field of logtable is of that type. It will interpret both instances of now() each time it runs. In the second example, the Netezza SQL main parser does not know what type now() should become and therefore it returns a data type of text containing the string now(). During the assignment to the local variable curtime, the NZPLSQL interpreter casts this string to the timestamp type by calling the text_out() and timestamp_in() functions for the conversion. If record fields are used in expressions or statements, the data types of fields should not change between calls to the same expression or statement.
Literals and Math Operations in Expressions

If your stored procedures perform math operations in expressions, carefully consider the supported ranges for literals and the possible implicit typecasts that the Netezza system may perform. These ranges and behaviors could cause unexpected results in complex math expressions. For example, all integer literals are 32-bit (signed) numbers by default, which supports a range of values from -2147483648 to 2147483647. If you perform math operations on a literal, use caution to check for overflows. For example, the following stored procedure defines a numeric value n, assigns it the largest supported value (2147483647) and then adds 1 to the value:
CREATE OR REPLACE PROCEDURE num() RETURNS BOOL LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE
2-10
20470-03
Rev.2
Expressions
n NUMERIC; BEGIN n := 2147483647; RAISE NOTICE 'n is %', n; n := 2147483647 + 1; RAISE NOTICE 'n is %', n; END; END_PROC;
If you create and run this stored procedure, the output is as follows:
NOTICE: NOTICE: n is 2147483647 n is -2147483648
The output demonstrates that when a value overflows its maximum range value, the value wraps to its minimum value and begins again. To avoid this counter-wrap behavior, you must either use a cast operation to cast n to a higher precision type, or assign values to intermediate values. For example, the following stored procedure creates the counter-wrap issue for the numeric value n, but then it uses casts to numerics or bigints to increase the range of valid values, as follows:
CREATE OR REPLACE PROCEDURE num() RETURNS BOOL LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE n NUMERIC; BEGIN n := 2147483647; RAISE NOTICE 'n is %', n; n := 2147483647 + 1; RAISE NOTICE 'n is %', n; n := 2147483647::numeric + 1; RAISE NOTICE 'n is %', n; n := 2147483647::bigint + 1; RAISE NOTICE 'n is %', n; n := 2147483647; n := n + 1; RAISE NOTICE 'n is %', n; END; END_PROC;
If you create and run this stored procedure, the output would be similar to the following:
NOTICE: NOTICE: NOTICE: NOTICE: NOTICE: n n n n n is is is is is 2147483647 -2147483648 2147483648 2147483648 2147483648
If you use floating point numbers in expressions, Netezza attempts to cast it into a numeric if possible, with a specific precision and scale that it calculates using internally defined casting rules. Because arithmetic operations in a stored procedure are evaluated by invoking the backend executor, they will be processed as SELECT statements. To more clearly see the calculated shape of the expression result, it can be helpful to use it to create a table, which can then be described using the \d command. Printing the results may not provide enough insight to the resulting datatypes. In the arithmetic expression that follows, Netezza casts the precision and scale based on internal Netezza SQL behavior rules:
20470-03
Rev.2
2-11
DEV(ADMIN)=> create table test as select (1 + 0.08/365) interest; INSERT 0 1 DEV(ADMIN)=> \d test Table "TEST" Attribute | Type | Modifier | Default Value ----------+--------------+----------+--------------INTEREST | NUMERIC(8,6) | | Distributed on hash: "INTEREST"
In the sample table, the Netezza internal casting rules evaluated the expression 1+0.08/ 365 and determined that the field would be a numeric value with 8 digits of precision and 6 of scale. The following command shows the actual value saved in the row:
DEV(ADMIN)=> select * from TEST; INTEREST ---------1.000219 (1 row)
Another example follows:

DEV(ADMIN)=> create table test2 as select (4 + 1/5) loanrt; INSERT 0 1 DEV(ADMIN)=> \d test2 Table "TEST2" Attribute | Type | Modifier | Default Value ----------+----------+----------+--------------LOANRT | INTEGER | | Distributed on hash: "LOANRT"
In the previous example, Netezza is evaluating three integer values (4, 1, and 5). The Netezza system uses integer as the type for the new column. If you display the column value, as follows, you can see that the decimal portion of the value was truncated:
DEV(ADMIN)=> select * from TEST2; LOANRT -------4 (1 row)
A similar example follows, but instead of the expression 1/2, this expression uses the numeric value .5 instead, as follows:
DEV(ADMIN)=> create table test3 as select (4 + .5) loanrt; INSERT 0 1 DEV(ADMIN)=> \d test3 Table "TEST3" Attribute | Type | Modifier | Default Value ----------+---------------+----------+--------------LOANRT | NUMERIC(3,1) | | Distributed on hash: "LOANRT" DEV(ADMIN)=> select * from TEST3; LOANRT -------4.5 (1 row)
2-12
20470-03
Rev.2
Expressions
In this example, the .5 value is interpreted as 0.5, and thus cast to numeric(3,1). In addition to the casts that can occur when math expressions are parsed, Netezza functions can also cause an implicit typecast. For example, the function sqrt() takes and returns a double precision value. The following example uses the function to define a table column:
DEV(ADMIN)=> create table test4 as select (sqrt(42)); INSERT 0 1 DEV(ADMIN)=> \d test4 Table "TEST4" Attribute | Type | Modifier | Default Value ----------+------------------+----------+--------------SQRT | DOUBLE PRECISION | | Distributed on hash: "SQRT" DEV(ADMIN)=> select * from TEST4; SQRT ----------------6.4807406984079 (1 row)
In the test4 example, the sqrt() function causes Netezza to cast the input integer value to a double and to return a double. Keep these behaviors in mind when you work with stored procedures that use arithmetic expressions to evaluate data. The implicit casts may not provide the value that you would expect if you evaluated the same arithmetic expression with a calculator. An example follows:
CREATE OR REPLACE PROCEDURE sp_expressions_numeric02() RETURNS NUMERIC LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE thisnum9_2 NUMERIC(9,2); million NUMERIC(9,2) := 1000000.00; thisnum18 NUMERIC(18); litespeed NUMERIC := 186282; thisnum38 NUMERIC(38); BEGIN /* The following expression causes implicit casts in the math evaluation, reducing the precision and scale of the result. */ thisnum9_2 := million * (1 + 0.08/365)^(365 * 20); RAISE NOTICE 'thisnum9_2 == %', thisnum9_2; /* The following expression uses an explicit cast to increase the precision and scale of the intermediate evaluation. */ thisnum9_2 := million * (1 + 0.08::numeric(20,15)/365)^(365 * 20); RAISE NOTICE 'thisnum9_2 == %', thisnum9_2; /* The following expression uses the numeric literal litespeed to convert the speed of light from miles per sec to miles per year. */ thisnum18 := litespeed * 60 * 60 * 24 * 365.25; RAISE NOTICE 'thisnum18 == %', thisnum18; /* The following expression uses the integer 186282 to convert lightspeed from miles per sec to miles per year. In the right-side evaluation, however, the values overflowed the
20470-03
Rev.2
2-13
upper limit of an int several times during evaluation, yielding incorrect results. */ thisnum38 := 186282 * 60 * 60 * 24 * 365.25; RAISE NOTICE 'thisnum38 == %', thisnum38; END; END_PROC;
Sample output follows:

NOTICE: NOTICE: NOTICE: NOTICE: thisnum9_2 == 4945731.93 thisnum9_2 == 4952164.15 thisnum18 == 5878612843200 thisnum38 == -396334376256
As this example shows, explicit casts during arithmetic evaluations and careful use of literals, constants, and types can help to increase the accuracy of the expressions used in your stored procedures.
Statements
The following sections describe the types of statements which are explicitly understood by the NZPLSQL parser. Any statements which are not specified using these conventions (and thus are not understood by the NZPLSQL parser) are assumed to be SQL commands and sent to the database engine to execute. The resulting query should not return any data.
Assignment
To assign a value to a variable or row/record field, use the assignment statement as follows:
identifier := expression;
If the expressions result data type does not match the variables data type but the types are compatible, or the variable has a size/precision that is known (as for char(20)), the result value will be implicitly cast by the NZPLSQL bytecode interpreter using the result types output-function and the variables type input-function. Note that this could potentially result in runtime errors generated by the types input functions. Several examples follow:
user_id := 20; tax := subtotal * 0.06;
For more information about Netezza casting rules and behaviors, see the IBM Netezza Database Users Guide.
Calling Another Procedure

All procedures defined in a Netezza database return a value; the default value is NULL. Thus, the normal way to call a procedure is to execute a SELECT query or to do an assignment (resulting in an NZPLSQL internal SELECT). However, there are cases where you might not be concerned with the procedure's result. In these cases, use the CALL statement to call the procedure, as follows:
CALL query
The CALL statement executes a SELECT query and discards the result. Identifiers such as local variables are still substituted into input parameters.
2-14
20470-03
Rev.2
Statements
There is a maximum stored procedure call recursion limit of 1024. If the call recursion exceeds this value, the following error message is returned:
ERROR: stored procedure recursion limit exceeded
Executing Dynamic Queries

There may be times when you want to generate dynamic queries inside your NZPLSQL procedures. Or, you may have procedures that are designed to generate other procedures. For these situations, NZPLSQL provides the EXECUTE IMMEDIATE statement:
EXECUTE IMMEDIATE query-string
The query-string value is a string of type text which contains the query to be executed. When working with dynamic queries, make sure that you escape any single quotes in NZPLSQL. A query run by an EXECUTE IMMEDIATE statement is prepared each time the statement is run. The query string can be dynamically created within the procedure to perform actions on different tables and fields. The results from SELECT queries are discarded by EXECUTE IMMEDIATE, and SELECT INTO is not currently supported within EXECUTE IMMEDIATE. So, the only way to extract a result from a dynamically-created SELECT is to use the FOR ... EXECUTE form, described in Iterating Through Records on page 2-20. An example statement follows:
EXECUTE IMMEDIATE 'UPDATE tbl SET' || quote_ident('fieldname') || ' = ' || quote_literal('newvalue') || ' WHERE ...';
This example demonstrates the use of the quote_ident and quote_literal functions. To ensure that strings are correctly processed for quotes or special characters, expressions containing column and table identifiers should be passed to quote_ident. Expressions containing values that should be literal strings in the constructed command should be passed to quote_literal. Both take the appropriate steps to return the input text enclosed in double or single quotes, respectively, with any embedded special characters properly escaped.
Obtaining Other Results Status

There are two variables that provide system status indicators: ROW_COUNT and LAST_OID.

ROW_COUNT is the number of rows processed by the last SQL query sent down to the SQL engine. LAST_OID is the object ID (oid) of the last row inserted by the most recent SQL query.
LAST_OID is useful only after an INSERT query, and then only when the insert happens on a catalog table. In practice, LAST_OID is not likely to be very useful. In addition to these variables, you can also use the FOUND and IS NULL variables to perform special conditional processing based on record results. For more information, see Assignments on page 2-19.
20470-03
Rev.2
2-15
Returning From a Procedure

The RETURN command allows you to return data from a procedure:
RETURN [expression]
The procedure terminates and the value of expression is returned to the upper executor. If expression is not provided, NULL is assumed. The return value of a procedure can be undefined. If control reaches the end of the toplevel block of the procedure without encountering a RETURN statement, NULL is assumed. The expressions result is automatically cast into the procedure's return type as described for assignments.
Control Structures
Control structures may be the most useful and important part of the NZPLSQL language. With NZPLSQL's control structures, you can manipulate SQL data in a very flexible and powerful way.
Conditional Control
You use IF statements to take action according to certain conditions. NZPLSQL has three forms of IF statements:

IF-THEN IF-THEN-ELSE IF-THEN-ELSE IF
Note: All NZPLSQL IF statements require a corresponding END IF statement. In ELSE-IF statements, you need two END IF statements: one for the first IF and one for the second (ELSE IF).
IF-THEN Statements
IF-THEN statements are the simplest form of an IF. The statements between THEN and END IF will be executed if the condition is true. Otherwise, the statements following END IF will be executed. An example follows:
IF v_user_id <> 0 THEN UPDATE users SET email = v_email WHERE user_id = v_user_id; END IF;
IF-THEN-ELSE Statements
IF-THEN-ELSE statements add to IF-THEN by letting you specify the statements that should be executed if the condition evaluates to FALSE. For example:
IF parentid IS NULL or parentid = '' THEN return fullname; ELSE return hp_true_filename(parentid) || '/' || fullname; END IF; IF v_count > 0 THEN INSERT INTO users_count(count) VALUES(v_count);
2-16
20470-03
Rev.2
Control Structures
return 't'; ELSE return 'f'; END IF;
You can nest IF statements as in the following example:

IF movies.genre = 'd' THEN film_genre := 'drama'; ELSE IF movies.genre = 'c' THEN film_genre := 'comedy'; END IF; END IF;
IF-THEN-ELSE IF Statement
When you use the "ELSE IF" statement, you are actually nesting an IF statement inside the ELSE statement. Thus you need one END IF statement for each nested IF and one for the parent IF-ELSE. For example:
IF movies.genre = 'd' THEN film_genre := 'drama'; ELSE IF movies.genre = 'c' THEN film_genre := 'comedy'; END IF; END IF;
While this form works, it can become a little tedious and error-prone if there are many alternatives to check. Thus, the language offers the alternative syntax using ELSIF or ELSEIF, as follows:
IF movies.genre = 'd' THEN film_genre := 'drama'; ELSIF movies.genre = 'c' THEN film_genre := 'comedy'; ELSIF movies.genre = 'a' THEN film_genre := 'action'; ELSIF movies.genre = 'n' THEN film_genre := 'narrative'; ELSE -- An uncategorized genre form has been requested. film_genre := 'Uncategorized'; END IF;
The IF-THEN-ELSIF-ELSE form offers some flexibility and eases the coding process when you need to check many alternatives in one statement. While it is equivalent to nested IFTHEN-ELSE-IF-THEN commands, it needs only one END IF statement.
Iterative Control
With the LOOP, WHILE, FOR, and EXIT statements, you can control the flow of execution of your NZPLSQL program iteratively.
Loop Statement
The LOOP statement defines an unconditional loop that repeats until terminated by an EXIT statement or a RETURN statement (which terminates the procedure and the loop). It has the following syntax:
20470-03
Rev.2
2-17
[<<label>>] LOOP statements END LOOP;
The optional label can be used by EXIT statements of nested loops to specify which level of nesting should be terminated.
EXIT Statement
The EXIT statement terminates a loop. It has the following syntax:
EXIT [ label ] [ WHEN expression ];
If you do not specify a label, the innermost loop is terminated and the statement following END LOOP is executed next. If you specify a label, it must be the label of the current or an upper level of nested loop or blocks. Then the named loop or block is terminated and control continues with the statement after the loops/blocks corresponding END. Examples:
LOOP -- some computations IF count > 0 THEN EXIT; -- exit loop END IF; END LOOP; LOOP -- some computations EXIT WHEN count > 0; END LOOP; BEGIN -- some computations IF stocks > 100000 THEN EXIT; END IF; END;
WHILE Statement
With the WHILE statement, you can loop through a sequence of statements as long as the evaluation of the condition expression is true.
[<<label>>] WHILE expression LOOP statements END LOOP;
For example:
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP -- some computations here END LOOP; WHILE NOT boolean_expression LOOP -- some computations here END LOOP;
2-18
20470-03
Rev.2
Working with Records
FOR Statement
Using the FOR statement, you can create a loop that iterates over a range of integer values.
[<<label>>] FOR name IN [ REVERSE ] expression .. expression LOOP statements END LOOP;
The variable name is automatically created as type integer and exists only inside the loop. The two expressions for the lower and upper bound of the range are evaluated only when entering the loop. The iteration step is always 1. Some examples of FOR loops:
FOR i IN 10..1 LOOP -- some expressions here RAISE NOTICE 'i is %',i; END LOOP; FOR i IN REVERSE 10..1 LOOP -- some expressions here END LOOP;
Working with Records

Records are similar to rowtypes, but they have no predefined structure. They are used in selections and FOR loops to hold one database row from a SELECT operation.
Declaration
Variables of type RECORD can be used for different selections. Accessing a record or an attempt to assign a value to a record field when there is no row in it results in a run-time error. To declare a RECORD variable:
name RECORD;
Assignments
You can use the following query to assign a complete selection into a record or row:
SELECT expressions INTO target FROM ...;
target can be a record, a row variable, or a comma-separated list of variables and recordfields or row-fields. Note that this is different from the SQL interpretation of SELECT INTO, which is that the INTO target is a newly created table. (If you want to create a table from a SELECT result inside a NZPLSQL procedure, use the equivalent syntax CREATE TABLE AS SELECT.) If a row or a variable list is used as the target, the selected values must exactly match the structure of the target(s) or a runtime error occurs. The FROM keyword can be followed by any valid qualification, grouping, or sorting that can be given for a SELECT statement. After a record or row has been assigned to a RECORD variable, you can use the "." (dot) notation to access fields in that record as follows:
DECLARE users_rec RECORD; full_name varchar;
20470-03
Rev.2
2-19
BEGIN SELECT * INTO users_rec FROM users WHERE user_id=3; full_name := users_rec.first_name || ' ' || users_rec.last_name;
There is a special variable named FOUND of type boolean that can be used immediately after a SELECT INTO to check whether an assignment was successful. The following example uses the NOT FOUND form to raise an exception if a SELECT INTO statement does not match on the requested input name:
SELECT * INTO myrec FROM EMP WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', myname; END IF;
FOUND is very similar to ROW_COUNT (described in Obtaining Other Results Status on page 2-15). For example, the following statement:
IF FOUND
is equivalent to this statement:

IF ROW_COUNT >= 1
You can also use the IS NULL (or ISNULL) conditionals to test whether a RECORD or ROW is NULL. If the selection returns multiple rows, only the first is moved into the target fields. All others are silently discarded. For example:
DECLARE users_rec RECORD; full_name varchar; BEGIN SELECT * INTO users_rec FROM users WHERE user_id=3; IF users_rec.homepage IS NULL THEN -- user entered no homepage, return "http://" return 'http://'; END IF; END;
Iterating Through Records

Using a special type of FOR loop, you can iterate through the results of a query and manipulate that data accordingly. The FOR IN loop syntax is as follows:
[<<label>>] FOR record | row IN select_clause LOOP statements END LOOP;
The record or row is assigned all the rows resulting from the select clause and the loop body is executed for each. An example follows:
DECLARE mviews RECORD; -- Instead, if you did: -- mviews cs_materialized_views%ROWTYPE; -- this record is ONLY usable for the cs_materialized_views table BEGIN CALL cs_log('Refreshing materialized views...'); FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP -- Now "mviews" has one record from cs_materialized_views RAISE EXCEPTION, 'Can't execute SQL while processing SQL for %', mview.my_name;
2-20
20470-03
Rev.2
Aborting and Messages
END LOOP; CALL cs_log('Done refreshing materialized views.'); return 1; end;
If the loop is terminated with an EXIT statement, the last assigned row is still accessible after the loop. The FOR-IN EXECUTE statement is another way to iterate over records:
[<<label>>] FOR record | row IN EXECUTE text_expression LOOP statements END LOOP;
This is similar to the previous form, except that the source SELECT statement is specified as a string expression. The main difference between the two is the syntax and use of variables to build the SQL to execute. Note that the first form is faster to evaluate.
Aborting and Messages

Use the RAISE statement to report messages and raise errors. The statement syntax follows:
RAISE level 'format' [, identifier [...]];
Inside format, the percent character (%) is used as a placeholder for a subsequent, comma-separated identifier. You can specify more than one % and identifier pair, as follows:
RAISE NOTICE 'Welcome % %', firstname, lastname;
In this example, the notice message substitutes the value of firstname for the first % character, and substitutes the value of lastname for the second % character. The message levels are as follows:

DEBUG messages only appear in pg.log. NOTICE messages are written to the database log and forwarded to the client application. EXCEPTION messages are written to the database log, forwarded to the client application as non-fatal messages, and usually abort the transaction if they are not caught.
An example of a RAISE statement follows:

RAISE NOTICE 'Calling cs_create_job(%)', job_id;
In the example, job_id will replace the % in the string and display the message to the client and in pg.log.
RAISE EXCEPTION 'Inexistent ID --> %', user_id;
This EXCEPTION statement will abort the transaction (if the exception is not caught) and write the message to the database log.
20470-03
Rev.2
2-21
Exceptions and Error Messages Support

By default, any error occurring in a NZPLSQL procedure aborts the execution of the procedure, and aborts the surrounding transaction as well. The system returns to the main loop to obtain the next query from the client application. Note that it is not possible to catch all exceptions, especially if that action should leave the database in a bad state. The NZPLSQL language uses the EXCEPTION statement to process exceptions:
EXCEPTION WHEN OTHERS THEN Handler_statements
You place the statement at the end of a block. If no error occurs, the EXCEPTION handler statements are not executed. The variable SQLERRM contains the text of an error message that has been caught. In the absence of an exception block, the exception propagates up to the next stored procedure in the call stack. So, for example, if sproc1 calls sproc2 which generates an exception, but sproc2 does not have an exception handler, then the system looks for a handler in sproc1. The system also looks at the enclosing block declarations. For example:
create or replace procedure sp_except01() returns BOOL LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE r record; BEGIN <<inner>> BEGIN SELECT * INTO r FROM NONEXISTENT; END; END; END_PROC; create or replace procedure sp_except02() returns BOOL LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN CALL sp_except01(); END; END_PROC; create or replace procedure sp_except03() returns BOOL LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN CALL sp_except02(); EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Caught exception'; END; END_PROC;
In these examples, the exception is generated in sp_except01, in the block inner. The system first checks for an exception handler for block inner, which is not found. Control passes to the parent context, which is the procedure sp_except01, and an exception handler is also not found there. Control then passes to sp_except02, and finally sp_except03, where an exception handler is found and used.
2-22
20470-03
Rev.2
Returning a Result Set
If an exception is not caught at any level, additional NOTICE-level log messages are sent to provide context about the error and where it occurred (line number and type of statement, unless the error is from a RAISE EXCEPTION statement). If you include exception handlers in stored procedures, the handlers will catch any errors and the errors are not displayed. For example:
CREATE PROCEDURE sp() RETURNS INTEGER LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN EXECUTE IMMEDIATE 'insert into NOTEXIST' || 'values(1,1)'; EXCEPTION WHEN OTHERS THEN END; END_PROC;
Assuming that NOTEXIST does not exist in the database, the query does not display any error output because the error was handled by the exception handler. To display the error, write the procedure as follows:
CREATE PROCEDURE sp() RETURNS INTEGER LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN EXECUTE IMMEDIATE 'insert into NOTEXIST' || ' values(1,1)'; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Got exception: %', SQLERRM; END; END_PROC;
When you run this query, it displays the following output:

NOTICE: Got exception: ERROR: Relation 'NOTEXIST' does not exist
Returning a Result Set

Typically, an NZPLSQL procedure returns a unique return value, but it can also return a result set in the form of a specified table. To create a stored procedure that returns a result set, do the following:

Define the stored procedure with a return value of RETURNS REFTABLE (<tablename>) to indicate that it returns a result set that looks like the specified table. Inside the body of the procedure, use the variable REFTABLENAME to refer to the results table.
The table specified in the RETURNS value must exist at the time that the stored procedure is created, although the table could be empty. The table will continue to exist after the stored procedure completes. You will not be permitted to drop the reference table while the stored procedure is defined. (That is, you must drop the stored procedure or modify it to return a different reference table before you can drop the table.) For example, the following stored procedure returntwo returns a result set using the reference table feature. The reference table that it uses, tbl, was previously defined using the following command:
CREATE TABLE tbl (i INT4, i2 INT4);
20470-03
Rev.2
2-23
A description of tbl follows:

DEV(ADMIN)=> \d tbl Table "TBL" Attribute | Type | Modifier | Default Value -----------+---------+----------+--------------I | INTEGER | | I2 | BIGINT | | Distributed on hash: "I"
After you confirm that the reference table exists, you can use the following command to define the stored procedure returntwo:
DEV(ADMIN)=> CREATE OR REPLACE PROCEDURE returntwo(timestamp) RETURNS REFTABLE(tbl) LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (1,1)'; EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (2,2)'; RETURN REFTABLE; END; END_PROC;
A sample call to the returntwo stored procedure follows:

DEV(ADMIN)=> EXECUTE PROCEDURE returntwo(now()); I | I2 ---+---1 | 1 2 | 2 (2 rows)
Note: You cannot specify a WHERE clause in a query which calls a stored procedure that returns a result set. When you call or invoke the stored procedure using a SQL command such as SELECT procedure(), CALL procedure(), EXECUTE procedure(), and so forth, the database does the following:

Generates a tablename TEMPFUNC<oid> where oid is the object ID of the procedure that was invoked Checks if the tablename exists; if it does, it issues a DROP TABLE <temp-table-name> command Issues a CREATE TEMPORARY TABLE <temp-table-name> as select * from <tablename> LIMIT 0 to create the table for the results set with no initial contents Returns the results of SELECT * from <temp-table-name> where proc(args) is NULL (Note that this is the only situation in which a stored procedure is allowed to be executed with a FROM clause and where the return value is used as part of a query.)
To use this in a procedure, you must insert your results in <temp-table-name> using the REFTABLENAME variable to obtain the name. This SQL command must be invoked dynamically in order to use the variable. Additionally, you must return NULL in your procedure by one of the following means:

RETURN REFTABLE; RETURN NULL;
2-24
20470-03
Rev.2
Advanced Development Topics
RETURN; Not specifying a RETURN clause.
If you do not return NULL, the procedure returns an error. The recommended method to return NULL is RETURN REFTABLE. One REFTABLE procedure can call another, but you will encounter unusual results if a REFTABLE procedure calls itself (either directly or recursively) because of the temporary table logic; therefore, avoid designing a REFTABLE procedure which calls itself. Cross-database access for a REFTABLE procedure should work without problem as the temporary table will be created in the local database; it will retrieve the shape of the REFTABLE definition in the other database. The SQL that is executed (for example, CREATE TEMPORARY TABLE, DROP TABLE, and so forth) uses the owner ID of the procedure as the effective user ID if EXECUTE AS OWNER is set; otherwise, if EXECUTE AS CALLER is set, the SQL uses the user ID of the account which calls or invokes the procedure.
Managing Large Datasets

If users run stored procedures on large datasets, there may be situations where there is not enough memory to hold the results of a select on that table. For example, some stored procedures could read each record from a table and take action on each record, as follows:
FOR rec in SELECT * from tablenm LOOP --perform processing steps END LOOP:
The SELECT operation runs first and caches its results in memory or as a temporary file on disk, depending upon the size of the result set. The procedure then applies the steps in the inner processing loop. If the table (tablenm) is very large, such as a table with millions of rows or one with many thousands of large rows, the temporary file could be a huge file which consumes the free disk space on the Netezza host. Use caution when your stored procedures process very large tables. Note: In the Netezza environment, these types of select loops that operate on single rows are not optimized for performance in the Netezza environment. Where possible, recast the loop to operate on record sets. For more information, see Query Processing in Loops on page 2-26.
Advanced Development Topics

The following sections describe some topics which leverage user-defined functions and stored procedures to perform advanced programming and scripting on the Netezza system.
Extending the Language with UDFs

If you or other users at your site create user-defined functions (UDFs) for your Netezza systems, you can use UDFs to extend the NZPLSQL language. These UDFs must be invoked using SQL that is designed to run only on the Netezza host inside Postgres. For details
20470-03
Rev.2
2-25
about the use of UDFs to extend NZPLSQL, refer to the IBM Netezza User-Defined Functions Developers Guide. Note that the guide is available only to members of the Netezza Developer Network (NDN); contact ndnsupport@netezza.com for more information.
Tips and Developer Best Practices

The following sections describe some common tips and best practices for stored procedures.
Control-C Interrupt Processing in Loops

If the procedure encounters an infinite loop, you can press Control-C to interrupt the loop. This key sequence should cause an exception (which is not catchable) and thus cause the loop and the procedure call to be interrupted and return.
Query Processing in Loops

Within stored procedures, there may be loop designs which appear to use concurrent queries to process the results. For example, the following procedure uses a SELECT to gather information, which is then processed for a possible DELETE action:
FOR rec in SELECT * from mytable LOOP IF rec.type = d THEN EXECUTE IMMEDIATE DELETE FROM mytable2 WHERE recid = || rec.id; END IF; END LOOP;
While it appears that the outer FOR loop SELECT and the inner EXECUTE IMMEDIATE queries are running at the same time, the SELECT query finishes and caches its results before the inner query begins. Thus, deleting records from the table in the inner loop will not impact the SELECT query in the outer loop or change its results. Note: Although it may be very common to perform row-at-a-time operations, as used in the preceding example, you can significantly improve the performace of these procedures by designing them to operate on sets of records rather than single row operations. For example, if you recast the example above to the following: DELETE from mytable2 where recid in (select recid from my table where type = 'd') ; This procedure design can take advantage of Netezzas massively parallel processing (MPP) environment to run much more quickly.
2-26
20470-03
Rev.2
CHAPTER 3
Creating and Managing Stored Procedures
Managing User Account Permissions Creating a Stored Procedure Calling or Invoking a Stored Procedure Altering a Stored Procedure Commenting on a Stored Procedure Dropping a Stored Procedure Showing Information About a Stored Procedure NzAdmin UI for Stored Procedures
This chapter describes the basic management and user tasks associated with stored procedures, such as managing permissions, creating procedures, invoking procedures, and altering and dropping stored procedures.
Managing User Account Permissions

Before you create any stored procedures, familiarize yourself with the required account permissions necessary to create and manage these objects. The Netezza admin user has full permission to manage and execute stored procedures. The database owner also has full permission to objects within the database that he or she owns. In addition, the owner (that is, the user who created the stored procedure) has permission to manage and execute the stored procedures that he or she creates. As the admin user or any user who has account management permissions, you can grant other users permission to create, manage, or execute the stored procedures on a Netezza system. You can assign permissions using the NzAdmin user interface, or by using the GRANT and REVOKE SQL commands. For details on managing users and groups, as well as assigning permissions using the NzAdmin interface, refer to the IBM Netezza System Administrators Guide. If you use SQL commands to manage account permissions, note some special formats and syntax for the commands. In the following examples, the term entity represents either a user or a user group. The term object represents the word PROCEDURE (for all stored procedure objects) or a specific stored procedure if object is specified as a full signature with the procedure name and argument type list.
3-1
Make sure that you specify the correct signature including the sizes for numeric and string datatypes, otherwise you will receive an error similar to the following:
ERROR: GrantRevokeCommand: existing UDX NAME(ARGS) differs in size of string/numeric arguments
Granting Create Permission

To grant Create Procedure administration permissions:
GRANT CREATE PROCEDURE TO entity;
For example, the following command grants Create Procedure permissions to the user bsmith:
GRANT CREATE PROCEDURE TO bsmith;
Granting All Object Permissions

To grant users or a group with all object permissions, use the following command:
GRANT ALL ON PROCEDURE TO entity;
This command gives the user or group the Alter, Drop, List, and Execute privileges for procedures, as well as other permissions such as Insert, Delete, and others which are not used for procedures. For example, the following command grants object permissions to the user bsmith:
GRANT ALL ON PROCEDURE TO bsmith;
Revoking Create Permission

To revoke Create Procedure administration permission:
REVOKE CREATE PROCEDURE FROM entity;
For example, the following command revokes Create Procedure permissions from the group analysts:
REVOKE CREATE PROCEDURE FROM GROUP analysts;
Managing Alter Permission

To grant or revoke Alter permission on an object:

GRANT ALTER ON object TO entity; REVOKE ALTER ON object FROM entity;
For example, to grant Alter permissions for the sample stored procedure returntwo to the user asmith:
GRANT ALTER ON returntwo(timestamp) TO asmith;
To revoke Alter permissions on the returntwo procedure from the group sales:
REVOKE ALTER ON returntwo(timestamp) FROM GROUP sales;
Managing Execute Permission

To grant or revoke Execute permission on an object:
GRANT EXECUTE ON object TO entity;
3-2
20470-03
Rev.2
Creating a Stored Procedure
For example, to grant Execute permissions for the sample procedure returntwo to the user bsmith, you can use the following command:
GRANT EXECUTE ON returntwo(timestamp) TO bsmith;
To revoke Execute permissions for the sample procedure from the group sales:
REVOKE EXECUTE ON returntwo(timestamp) FROM GROUP sales;
Managing Drop Permission

To grant or revoke Drop permissions on an object:

GRANT DROP ON object TO entity; REVOKE DROP ON object FROM entity;
For example, to grant Drop permissions for the sample procedure returntwo to the user pcollins:
GRANT DROP ON returntwo(timestamp) TO pcollins;
To revoke Drop permissions on the returntwo procedure from the user bsmith:
REVOKE DROP ON returntwo(timestamp) FROM bsmith;

The process to create a stored procedure is very straightforward; typically, the time-consuming work is in the design of the stored procedure, as well as the debugging and testing of the procedure. This section describes the following key steps for creating a stored procedure:

Design a stored procedure Create a stored procedure
Design a Stored Procedure

The first step is to design the procedure itself. Make sure that you outline the basic purpose and actions of the procedure, consider helpful error logging, and plan for exception handling. Review the NZPLSQL language and statements to create the body of the procedure.
Create a Stored Procedure

After you have created the body of the stored procedure, you add it using the CREATE [OR REPLACE] PROCEDURE command. You enter this command at a SQL command prompt. The CREATE PROCEDURE command creates a new procedure. To use this command, you must be the admin user, own the database, or have the Create Procedure privilege. The CREATE OR REPLACE PROCEDURE command will create a new procedure if one does not already exist with the specified signature, or will update an existing procedure with the matching signature for the new (or different) field values. To replace a procedure, you must have the Alter privilege.
20470-03
Rev.2
3-3
For example, the following procedure customer writes a customer name string to the database log using the RAISE NOTICE statement:
TEST(USR)=> CREATE PROCEDURE customer() RETURNS INT4 LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN RAISE NOTICE 'The customer name is alpha'; END; END_PROC; CREATE PROCEDURE
Since the execution user permissions were not specified on the command line, the command uses the default of EXECUTE AS OWNER. If you want to change the customer procedure to specify a new return value, you could use a CREATE OR REPLACE PROCEDURE command similar to the following, but note that you must specify all the required fields (such as language and the complete procedure body) even if their values did not change, as follows:
TEST(USR)=> CREATE OR REPLACE PROCEDURE customer() RETURNS INT8 LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN RAISE NOTICE 'The customer name is alpha'; END; END_PROC; CREATE PROCEDURE
You can also use the ALTER PROCEDURE command to modify certain characteristics of a defined procedure, as described in Altering a Stored Procedure on page 3-10.
Procedure Signatures
Each stored procedure has a signature; that is, a unique identification in the form <procedure_name>(<argument_type_list>). Signatures must be unique within the same database; that is, they cannot duplicate the signature of another stored procedure. The <argument_type_list> component does not consider data type sizes to be differentiators. For example, you cannot create two procedures called myproc(numeric(3,2)) and myproc(numeric(4,1)) in the same database. The larger numeric size is not sufficient to create a unique signature. If there are common use-cases where a procedure must accept different sized strings or numerics, you could design the procedure to accept the largest of the possible values, or you could create a new stored procedure with a different name to process the different data size, for example:
TEST(USR)=> CREATE PROCEDURE myproc_lgnum(numeric(4,1)) ...
Overloading Procedures
You can create stored procedures that have the same procedure name, but which have different argument signatures and return types. This process is referred to as overloading the procedure definition. For example, assume that you have a procedure called customer_name that could take two or three input strings which represent a customers first, middle (if specified), and last name.
3-4
20470-03
Rev.2
Some sample procedure definitions follow:

TEST(USR)=> CREATE PROCEDURE customer_name(VARCHAR(30), VARCHAR(30)) ... TEST(USR)=> CREATE PROCEDURE customer_name(VARCHAR(30), VARCHAR(30), VARCHAR(30)) ...
If a user calls customer_name with two input strings, the system uses the first (two argument) procedure. If the user specifies three input strings, the procedure uses the second procedure that accepts three input strings. Overloading allows you to support different combinations of input values and/or return types. However, overloading and uniquely named but similar procedures have a maintenance overhead; if you need to update or redesign the body of the procedure, you have to update each procedure definition with the changes that you want to make.
Understanding Size-Specific, Generic, and Variable Argument Procedures

In the signature of a stored procedure, the <argument_type_list> can use three general forms:

Size-specific arguments Generic arguments Variable arguments
The following sections describe these three formats and the benefits and considerations for using that type.
Size-Specific Arguments
With size-specific arguments, you declare the type and size of all input arguments, as well as the type and size of the return value. Specific datatype size declarations are useful for error-checking of the input arguments and return values, but they can be somewhat limiting if your procedure processes strings or numerics which could vary in size when you run a query. For example, the following example creates a stored procedure that takes a string of up to 20 VARCHAR characters:
TEST(USR)=> CREATE PROCEDURE customer(VARCHAR(20)) RETURNS INT4 LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN remaining text omitted for example...
Constant datatype sizes can result in implicit casts, such as casting a smaller input value to fit a larger declared size (for example, it could increase the precision of a numeric or add padding to strings). If you choose too small a size, you risk loss of precision if Netezza casts a larger input numeric to a smaller numeric or truncates input strings that exceed the input argument size.
Generic-Size Arguments
Generic-size (or any-size) arguments offer more flexibility for strings and numerics. You can declare character strings or numerics using the ANY keyword in the signature (or in the return value). For example:
20470-03
Rev.2
3-5
TEST(USR)=> CREATE PROCEDURE customer(VARCHAR(ANY)) RETURNS VARCHAR(ANY) LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN remaining text omitted for example...
The stored procedure accepts an input string of up to 64,000 characters (the maximum for a VARCHAR). Within the body of the stored procedure, the code must process the strings and numerics with the plan that you could receive a string of any valid length. That is, you can check and obtain their size, process them as needed, and return the value for the procedure. Generic-size arguments help you to avoid specific limits for the input strings and numerics, or to use overly large or maximum size values that result in unnecessary resource allocation for the procedure. This format can also reduce the need to register and maintain similar procedures that take different input arguments or have different return values, as well as possible casting of input values. Supported Generic Argument Types You use the ANY keyword to indicate that an argument is generic. The following datatypes support the ANY keyword as a size specifier:

CHAR or NCHAR VARCHAR or NVARCHAR NUMERIC
For example, to specify a numeric datatype of precision 10 and scale 2, you specify it as NUMERIC(10,2). To specify a numeric datatype that can take any size, you specify is as NUMERIC(ANY). Likewise, to specify a variable character string that can take any size, you declare it as VARCHAR(ANY). Generic Arguments in the Procedure Signature You can define generic arguments as well as the standard datatype-and-size-specific arguments in the signature of a stored procedure. The Netezza software verifies that all the input arguments match the required number and datatypes of the signature. For arguments that have a specific size, the Netezza software also confirms that the size of the input value matches the defined signature size. If necessary, the Netezza software casts the input values to match the size specified in the signature. For example, if you declare a string of 20 characters [CHAR(20)] in a signature, the Netezza software implicitly truncates an input string that is longer than 20 characters or adds padding if the input string is less than 20 characters. For generic arguments, the argument values are passed to the procedure without any casting or changes. For example, if you declare a CHAR(ANY) input value, the procedure accepts character strings of any length up to the supported maximum; it checks to make sure that the input value is a valid character string and that it occurs in the expected place of the signature. The Netezza software performs some implicit castings for the input values. For example, if you define an input argument as VARCHAR(ANY) in the signature, but you pass an input of CHAR(200) to the procedure, the procedure casts the CHAR(200) to VARCHAR(200). The procedure uses the datatype of the signature and the size of the input value to determine the casting change.
3-6
20470-03
Rev.2
Generic Procedure Return Value If you use ANY for a return value size, your procedure calculates the size of the numeric or string return value from the RETURN expression and simply returns it. Registering Generic Procedures When you register a stored procedure that uses generic arguments, you use the keyword ANY to declare character or numeric datatypes as generic. For a complete description of the command, refer to CREATE [OR REPLACE] PROCEDURE on page A-6. An example follows:
MYDB(USR)=> CREATE PROCEDURE itemfind(NUMERIC(ANY)) RETURNS CHAR(ANY) LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN /* Body of procedure... intentionally omitted from example*/ END; END_PROC;
In this example, the itemfind() procedure takes an input numeric datatype of any valid size and returns char value of any size.
Variable Arguments
Variable-argument procedures offer even more flexibility than generic-size arguments. With variable argument procedures, you specify only the VARARGS keyword in the argument_type_list. Users can specify from 0 to 64 input values of any supported data type as input arguments. For example:
TEST(USR)=> CREATE PROCEDURE customer(VARARGS) RETURNS VARCHAR(ANY) LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN remaining text omitted for example...
Within the body of the stored procedure, the code must process the input values and manage them as needed. For more information, see Argument List and Variables on page 2-4. Variable argument procedures allow you to create one procedure that can be used for different combinations of input types. This simplifies the development of stored procedures and reduces the need to create overloaded procedure definitions that perform the same task for different types and numbers of arguments.
Obfuscating the Procedure Body

As shown in CREATE PROCEDURE examples, the procedure body is entered in readable text format. Users who have permission to show the procedure can use the VERBOSE option to review the procedure body (see Showing Information About a Stored Procedure on page 3-12). If your procedure body contains intellectual property, copyrighted material, or other algorithms that you do not want in clear text, you can obfuscate the body text.
20470-03
Rev.2
3-7
Creating Obfuscated Procedures Using a Wrapping Procedure

A common way to create an obfuscated stored procedure is by creating a stored procedure wrapper that takes the procedure definition and the procedure body, and creates the obfuscated procedure, as in the following example:
TEST(USR)=> CREATE OR REPLACE PROCEDURE wrap_proc(text, text) RETURNS BOOL LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE proc alias for $1; body alias for $2; enc text; sql text; BEGIN enc := wrap_nzplsql(body); sql := proc || '' || '' || quote_literal(enc) || ''; RAISE NOTICE '%;', sql; EXECUTE IMMEDIATE sql; return true; END; END_PROC; CREATE PROCEDURE
Call the wrap_proc procedure and specify the CREATE OR REPLACE main definition in the first input value; then specify the BEGIN PROC/END PROC contents in the second input value. You must surround the main definition (the first input value) with single quotes. Do not enclose the second input value (the procedure body) in quotes because the wrap_nzplsql built-in procedure takes the text just as it would be specified for a CREATE OR REPLACE PROCEDURE command. An example follows:
TEST(USR)=> CALL wrap_proc('CREATE OR REPLACE PROCEDURE customer() RETURNS INT4 LANGUAGE NZPLSQL AS', BEGIN_PROC BEGIN RAISE NOTICE 'The customer name is alpha'; END; END_PROC); NOTICE: CREATE OR REPLACE PROCEDURE customer() RETURNS INT4 LANGUAGE NZPLSQL AS'TlpQU1FMV1JBUDEwWWk5NUhrQzVhR0xyRFRxTWR3VE5sQT09JEdFQ1B5LzVkSU1KMTI 1a0dUK3NTWjlkb3ZGL3ZHalhpVExPVG5UajRkK3gxSkxvZVhKejdZQmJOVHN0aU1waFRlb mhoaWtYdHJUTVkKUUNrWDY5Nko5Rms2NlBIYUxra21xeWNZYXdWclBCQT0='; wrap_proc ----------t (1 row)
When you call an obfuscated procedure, the system uses internal routines to read the obfuscated body text and run the procedure. The behavior and output of the obfuscated procedure is identical to a cleartext version of the procedure, for example:
TEST(USR)=> CALL customer(); NOTICE: The customer name is alpha customer ---------(1 row)
3-8
20470-03
Rev.2
Creating Obfuscated Procedure Body Text

As an alternative to using the wrap_proc procedure method, you could also create the obfuscated body of the procedure in a separate step as follows:
TEST(USR)=> CALL wrap_nzplsql(BEGIN_PROC BEGIN RAISE NOTICE 'The customer name is alpha'; END; END_PROC); wrap_nzplsql ---------------------------------------------------------------------TlpQU1FMV1JBUDEwVE5jZlh5TnpYbndCNkV5VFFMRTBiQT09JGE5N2p6ZEdJSVZwTTRrWm RRM0I3WmUxZERZeWd6YkdjTWkxTzQrL1dCMmpqRGQvak9lUzFQQjArNGdlM08yZVdxUjRI MTFaTnROUmwKdk5xSm0wb1RPZz09 (1 row)
The wrap_nzplsql built-in procedure generates the obfuscated body text, which you can input directly to a CREATE OR REPLACE PROCEDURE command, as follows:
TEST(USR)=> CREATE OR REPLACE PROCEDURE customer() RETURNS INT4 LANGUAGE NZPLSQL AS 'TlpQU1FMV1JBUDEwVE5jZlh5TnpYbndCNkV5VFFMRTBiQT09JGE5N2p6ZEdJSVZwTTRrW mRRM0I3WmUxZERZeWd6YkdjTWkxTzQrL1dCMmpqRGQvak9lUzFQQjArNGdlM08yZVdxUjR IMTFaTnROUmwKdk5xSm0wb1RPZz09'; CREATE PROCEDURE
As shown in the example, you must enclose the obfuscated body text in single-quotes for the CREATE OR REPLACE PROCEDURE command.
Creating Obfuscated Procedure Definitions for a Registration Script

If you plan to create a script that users will run to register your stored procedures in their databases, you could use a wrapping procedure that takes as input the procedure definition and body, and which returns a CREATE OR REPLACE PROCEDURE command that has obfuscated body text. For example:
TEST(USR)=> CREATE OR REPLACE PROCEDURE return_wrap(TEXT, TEXT) RETURNS TEXT LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE proc alias for $1; body alias for $2; enc text; sql text; BEGIN enc := wrap_nzplsql(body); sql := proc || ' ' || '' || quote_literal(enc) || ''; return sql; END; END_PROC; CREATE PROCEDURE
Similar to the wrap_proc example, specify the CREATE OR REPLACE main definition as a quoted text string in the first input value, and specify the unquoted BEGIN PROC/END PROC contents in the second input value. An example follows:
TEST(USR)=> CALL return_wrap('CREATE OR REPLACE PROCEDURE customer() RETURNS INT4 LANGUAGE NZPLSQL AS', BEGIN_PROC BEGIN RAISE NOTICE 'The customer name is alpha'; END; END_PROC); return_wrap ---------------------------------------------------------------------CREATE OR REPLACE PROCEDURE customer() RETURNS INT4 LANGUAGE NZPLSQL
20470-03
Rev.2
3-9
AS 'TlpQU1FMV1JBUDEwaTVJeHJnV1BLd1o0ZDVtNEtNMGxKQT09JCtiSlVDS1NHbkVrdVZja 01JR3Nrc2dTZlExWDdkaUpDeHdZWUp2dmlrUkZIeEpEQnR6dE1JSHMxOHRTR08xMG1IczJ FSk92R2F0Ti8KMGhKTGlUUEdIZz09' (1 row)
The return value is a CREATE OR REPLACE PROCEDURE command that you could use in your registration script to define your stored procedure without displaying the procedure body text to the script user.
Calling or Invoking a Stored Procedure

To invoke or execute the sample stored procedure customer(), you can use any of the following SQL query forms:
TEST(USR)=> TEST(USR)=> TEST(USR)=> TEST(USR)=> TEST(USR)=> CALL customer(); EXEC customer(); EXECUTE customer(); EXECUTE PROCEDURE customer(); SELECT customer();
Any of these commands cause the procedure to output the following:

NOTICE: The customer name is alpha CUSTOMER ---------(1 row)
For more information about the CALL and EXEC[UTE[ PROCEDURE]] commands, refer to Appendix A, SQL Reference.
Altering a Stored Procedure

After you define a stored procedure on the Netezza system, you can use the ALTER PROCEDURE command to change some aspects of the procedure. For more information about the command, see ALTER PROCEDURE on page A-1. You can modify a stored procedure to change the following aspects of the procedure:

RETURNS value Execution user property (EXECUTE AS OWNER versus EXECUTE AS CALLER) The body of the procedure The owner of the procedure
You cannot change the procedure name or argument type list. You must drop the existing procedure and create a new procedure with the new name and/or argument type list. For example, the following sample commands could be used to change the customer procedure. To change the return value type, use a command similar to the following:
TEST(USR)=> ALTER PROCEDURE customer() RETURNS INT8; ALTER PROCEDURE
To change the owner of the procedure to user, use a command similar to the following:
TEST(USR)=> ALTER PROCEDURE customer() OWNER TO user; ALTER PROCEDURE
3-10
20470-03
Rev.2
Commenting on a Stored Procedure
To change the user execution property to EXECUTE AS CALLER, use a command similar to the following:
TEST(USR)=> ALTER PROCEDURE customer() EXECUTE AS CALLER; ALTER PROCEDURE
To change the procedure definition, use a command similar to the following:

TEST(USR)=> ALTER PROCEDURE customer() AS BEGIN_PROC BEGIN RAISE NOTICE 'The customer name is beta'; END; END_PROC; ALTER PROCEDURE
Commenting on a Stored Procedure

As a best practice, Netezza recommends that you add some descriptive comments on stored procedures using the COMMENT ON command capability. For example:
COMMENT ON PROCEDURE customer() IS 'A procedure that displays a customer name.'; COMMENT
A Netezza SQL query user can display these comments using the nzsql \dd <name> command switch, or the \dd switch which will show all comments for all procedures. As a best practice, consider using a template of comments for all proceduresincluding information about the author, version, and descriptionin the following format:
COMMENT ON PROCEDURE <procedure name> (<argument type list>) IS 'Author: <name> Version: <version> Description: <description>';
For example:
COMMENT ON PROCEDURE customer() IS 'Author: bsmith Version: 1.0 Description: A procedure that writes a customer name to the database log file.';
To comment on a stored procedure, you must either be the Netezza admin user, the owner of the procedure, or you must have Alter permissions for procedure objects. For more information about COMMENT ON, refer to the IBM Netezza Database Users Guide. Make sure that you specify a full procedure name and argument list, including correct sizes for numeric and string datatypes. Otherwise you will receive an error similar to the following:
Error: CommentProcedure: existing procedure name(argument type list) differs in size of string/numeric arguments
Dropping a Stored Procedure

You can remove a stored procedure from the Netezza system using the DROP PROCEDURE command. For more information about the command, see DROP PROCEDURE on page A-9.
20470-03
Rev.2
3-11
For example, to drop the customer procedure, use a command similar to the following:
DROP PROCEDURE customer(); DROP PROCEDURE
Showing Information About a Stored Procedure

You can use the SHOW PROCEDURE command to display information about a stored procedure. A sample command follows for the example customer procedure:
MYDB(USR)=> SHOW PROCEDURE customer; RESULT | PROCEDURE | BUILTIN | ARGUMENTS ---------+-----------+---------+----------INTEGER | CUSTOMER | f | () (1 row)
The sample output shows the return value (RESULT), the procedure name, f (false) to indicate that this is a user-defined procedure (not a built-in or system-supplied procedure), and the argument list for the procedure (an empty list in this example). The command displays information for any procedures with names that begin with the specified characters. For example, if you have two procedures named customer and customerid, this example command displays information for both procedures. The command also offers a VERBOSE mode which displays more information about the procedure, including the procedure body, comments (if supplied), owner, execution user, and other information. If the procedure is obfuscated, the procedure body will not be in clear text; it will be in an unreadable format. For details about the SHOW PROCEDURE command, see SHOW PROCEDURE on page A-10.
NzAdmin UI for Stored Procedures

If you use the NzAdmin administrative interface, you can view information about the stored procedures defined on a Netezza system and manage them. You cannot add or alter a stored procedure from the NzAdmin interface, but you can display its properties, change its owner, drop the procedure, and manage user and group permissions for procedures. To view the stored procedures, go to the Database tab of NzAdmin. Select a database and double-click Procedures to list the stored procedures that are defined in that database.
Figure 3-1: The NzAdmin Stored Procedures Window
3-12
20470-03
Rev.2
NzAdmin UI for Stored Procedures
The Procedures list shows the signature, return type, owner, and creation date of each procedure. Double-click a procedure to obtain information about the procedure definition, or to view or manage the privileges for the procedure. For more information about using the NzAdmin interface, see the IBM Netezza System Administrators Guide.
20470-03
Rev.2
3-13
3-14
20470-03
Rev.2
APPENDIX
SQL Reference
This appendix describes the Netezza SQL commands that relate to the creation and management of stored procedures. Table A-1 lists the SQL commands. Table A-1: Stored Procedure SQL Commands Command ALTER PROCEDURE CALL or EXEC[UTE [ PROCEDURE]] Description Changes a stored procedure. Runs a stored procedure. More Information See ALTER PROCEDURE on page A-1. See CALL and EXEC[UTE [PROCEDURE]] on page A-4 See CREATE [OR REPLACE] PROCEDURE on page A-6. See DROP PROCEDURE on page A-9. See SHOW PROCEDURE on page A-10.
CREATE [OR REPLACE] Adds or updates a stored PROCEDURE procedure. DROP PROCEDURE SHOW PROCEDURE Drops or deletes a stored procedure. Displays information about stored procedures.
ALTER PROCEDURE
Use the ALTER PROCEDURE command to change a stored procedure. You can change the procedures return value, execution setting, owner, and procedure body, but you cannot change the name or argument list using this command. (You can add or remove the VARARGS value in an otherwise empty argument list.) To change a stored procedures name and/or argument list, you must drop the procedure and create a procedure with the new name and/or argument type list.
Synopsis
Syntax:
ALTER PROCEDURE <name> (<args>) [ RETURNS <type>] [ EXECUTE AS OWNER | EXECUTE AS CALLER ] [AS <procedure_body>]; ALTER PROCEDURE <name> (<args>) OWNER TO <user>;
A-1
Inputs
The ALTER PROCEDURE command takes the following inputs: Table A-2: ALTER PROCEDURE Input Input name args Description The name of the stored procedure that you want to change. You cannot change the name of the procedure. A list of input argument datatypes for the stored procedure. You could also specify the VARARGS value to create a variable argument procedure where users could 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. You cannot change the argument list or sizes. You can remove VARARGS from the argument list, or add it to an otherwise empty argument list. Specifies the type of data returned by the procedure. The <type> value can be a Netezza data type or the value REFTABLE (<tablename>) to indicate that it returns a result set that looks like the specified table. The table must exist, and it continue to exist after the procedure. The table could be empty, but it must exist in the database. For more information about returning a result set, see Returning a Result Set on page 2-23. If specified, the stored procedure uses the procedure owner ID for all access control and permission checks. This is the default. If specified, the stored procedure uses the ID of the user who called the procedure for all access control and permission checks. Specifies the text or body of the procedure. The body must be enclosed with single quotes or enclosed by a BEGIN_PROC/END_ PROC pair. When you alter the procedure, you can obfuscate the body to mask the content from users who have permission to show the procedure. For more information, see Obfuscating the Procedure Body on page 3-7.
RETURNS <type>
EXECUTE AS OWNER EXECUTE AS CALLER procedure_body
Outputs
The ALTER PROCEDURE command has the following output Table A-3: ALTER PROCEDURE Output Output ALTER PROCEDURE Description The message that the system returns if the command is successful.
A-2
20470-03
Rev.2
ALTER PROCEDURE
Table A-3: ALTER PROCEDURE Output Output ERROR: replacing procedure: permission denied. Error: FunctionAlter: existing UDX NAME(ARGS) differs in size of string/numeric arguments Description The message indicates that the user does not have Alter permission on the procedure. For more information, see Managing User Account Permissions on page 3-1. This error indicates that a stored procedure exists with the name but has different sizes specified for string or numeric arguments. To alter the stored procedure, make sure that you specify the exact argument type list with correct sizes. This error indicates that the specified procedure name does not exist in the database. You cannot specify both the VARARGS value and any other argument value in the arguments list. The VARARGS value is mutually exclusive.
ERROR: FunctionAlter: function NAME does not exist with that signature ERROR: Can't specify arguments to a varargs procedure
Description
You cannot alter a stored procedure that is currently in use in an active query. After the active querys transaction completes, the Netezza system will process the ALTER PROCEDURE command.
Privileges Required
To alter a procedure, you must meet one of the following criteria:

You must have the Alter privilege on the PROCEDURE object. You must have the Alter privilege on the specific procedure. You must own the procedure. You must be the database admin user or own the current database.
Common Tasks
You can use the ALTER PROCEDURE command to change the execution user ID of the procedure, its return value, or the procedure body itself. You can also use the ALTER PROCEDURE command to change the owner of a procedure as follows: ALTER PROCEDURE <name> (<arguments>) OWNER TO <name>;
Related Commands
See CALL and EXEC[UTE [PROCEDURE]] on page A-4 to invoke procedures. See CREATE [OR REPLACE] PROCEDURE on page A-6 to create procedures. See DROP PROCEDURE on page A-9 to drop procedures. See SHOW PROCEDURE on page A-10 to display information about the procedure.
20470-03
Rev.2
A-3
Usage
The following provides sample usage.
To change the execution ID from over to caller, enter:

system(admin)=> ALTER PROCEDURE myproc(int4) EXECUTE AS CALLER;
CALL and EXEC[UTE [PROCEDURE]]

Use the CALL command, EXEC command, EXECUTE command, or EXECUTE PROCEDURE command to invoke a stored procedure on a Netezza host. You can also use the SELECT command. The commands are identical in behavior, inputs, and outputs. The command variations provide for some compatibility with other procedural language invocation methods.
Synopsis
Syntax:
CALL procedure_name(arguments) EXEC procedure_name(arguments) EXECUTE procedure_name(arguments) EXECUTE PROCEDURE procedure_name(arguments) SELECT procedure_name(arguments)
Inputs
The CALL and EXEC[UTE [PROCEDURE] commands take the following inputs: Table A-4: CALL and EXEC[UTE [PROCEDURE]] Input Input procedure_name arguments Description The name of the stored procedure that you want to invoke. Specifies a list of constant or literal arguments to the procedure. The arguments may be results of functions, as long as the functions take only constant/literal arguments as well.
Outputs
The CALL and EXEC[UTE [PROCEDURE] commands have the following outputs: Table A-5: CALL and EXEC[UTE [PROCEDURE]] Output Output ERROR: EXECUTE PROC: Permission denied. Description This error indicates that the current user account does not have Execute permission for the stored procedure. For more information about permissions, see Managing User Account Permissions on page 3-1.
A-4
20470-03
Rev.2
CALL and EXEC[UTE [PROCEDURE]]
Table A-5: CALL and EXEC[UTE [PROCEDURE]] Output (continued) Output Description
This message indicates that the user entered incorrect arguments ERROR: Function 'NAME(ARGS)' does for the stored procedure. A procedure of that name exists, but it is expecting different input arguments. not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts
Description
The following sections describe the privileges and related commands.
Privileges Required
To invoke a stored procedure, you must meet one of the following criteria:

You must have the Execute privilege on the PROCEDURE object. You must have the Execute privilege on the specific procedure. You must own the procedure. You must be the database admin user or own the current database.
Related Commands
See ALTER PROCEDURE on page A-1 to change procedures. See CREATE [OR REPLACE] PROCEDURE on page A-6 to create procedures. See DROP PROCEDURE on page A-9 to drop procedures. See SHOW PROCEDURE on page A-10 to display information about the procedure.
Usage
The following examples provide some sample usage:
MYDB(USER)=> MYDB(USER)=> MYDB(USER)=> MYDB(USER)=> CALL updateacct(); EXEC myproc(); EXECUTE inventorysearch(umbrellas); EXECUTE PROCEDURE updateacct();
You can also use the SELECT command to run a procedure; however, you cannot specify a FROM clause. For example:
MYDB(USER)=> SELECT updateacct(); MYDB(USER)=> SELECT inventorysearch(umbrellas);
20470-03
Rev.2
A-5
CREATE [OR REPLACE] PROCEDURE

Use the CREATE PROCEDURE command to create a new stored procedure. Use CREATE OR REPLACE PROCEDURE to create a new stored procedure or to update an existing procedure with a new return type, execution permissions, or procedure body.
Synopsis
Syntax:
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 A-6: CREATE OR REPLACE PROCEDURE Input Input name Description The name of the stored procedure that you want to create or replace. This is the SQL identifier that will be used to invoke the procedure in a SQL expression. If the stored procedure already exists, you cannot change the name using the CREATE OR REPLACE command. Specifies a list of fully-specified argument data types. You could also specify the VARARGS value to create a variable argument procedure where users could 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 already exists, you cannot change the argument type list using 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. Specifies the type of data returned by the procedure. The <type> value can be a Netezza data type or the value REFTABLE (<tablename>) 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. For more information about returning a result set, see Returning a Result Set on page 2-23. If specified, the stored procedure runs using the procedure owner ID for all access control and permission checks. This is the default. If specified, the stored procedure runs using the ID of the user who called the procedure for all access control and permission checks.
arguments
RETURNS <type>
EXECUTE AS OWNER EXECUTE AS CALLER
A-6
20470-03
Rev.2
CREATE [OR REPLACE] PROCEDURE
Table A-6: CREATE OR REPLACE PROCEDURE Input (continued) Input LANGUAGE procedure_body Description Specifies the programming language used for the procedure. The default and only supported value is NZPLSQL. Specifies the text of the procedure and must be enclosed with single quotes 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. For more information, see Obfuscating the Procedure Body on page 3-7.
Outputs
The CREATE [OR REPLACE] PROCEDURE command has the following output: Table A-7: CREATE [OR REPLACE] PROCEDURE Output Output CREATE PROCEDURE ERROR: creating procedure: permission denied. Description The message that the system returns if the command is successful. The message indicates that the user does not have Create Procedure permission. For more information, see Managing User Account Permissions on page 3-1.
ERROR: User 'username' is not The system returns this message if your user account allowed to create/drop does not have permission to create a stored procedure. procedures. ERROR: Synonym 'name' already exists ERROR: ProcedureCreate: procedure NAME already exists with the same signature NOTICE: FunctionCreate: existing UDX NAME(ARGS) differs in size of string/numeric arguments ERROR: Can't specify arguments to a varargs procedure The system returns this message if a synonym already exists with the name that you specified for the stored procedure. This error is returned when you issue a CREATE PROCEDURE command and a stored procedure with the same name and argument type list already exists in the database. Use CREATE OR REPLACE PROCEDURE instead. This message indicates that a stored procedure already 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, you should check the signature and ensure that it is correct. You cannot specify both the VARARGS value and any other argument value in the arguments list. The VARARGS value is mutually exclusive.
20470-03
Rev.2
A-7
Description
When you create a stored procedure, note that the stored procedures signature (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 using 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 a stored procedures name and/or argument type list, you must drop the stored procedure and then create a stored procedure with the new name and/or argument type list. You cannot replace a stored procedure that is currently in use in an active query. After the active querys transaction completes, the Netezza system will process 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.
Related Commands
See ALTER PROCEDURE on page A-1 to change procedures. See CALL and EXEC[UTE [PROCEDURE]] on page A-4 to invoke procedures. See DROP PROCEDURE on page A-9 to drop procedures. See SHOW PROCEDURE on page A-10 to display information about the procedure.
Usage
To create a new procedure called customername:

system(admin)=> CREATE OR REPLACE PROCEDURE customer() RETURNS INT8 LANGUAGE NZPLSQL AS BEGIN_PROC BEGIN RAISE NOTICE 'The customer name is alpha'; END; END_PROC;
A-8
20470-03
Rev.2
DROP PROCEDURE
DROP PROCEDURE
Use the DROP PROCEDURE command to remove an existing stored procedure from a database.
Synopsis
Syntax:
DROP PROCEDURE <name> (<arguments>)
Inputs
The DROP PROCEDURE command takes the following inputs: Table A-8: DROP PROCEDURE Input Input name arguments Description The name of the stored procedure that you want to drop. A list of input arguments to uniquely identify the stored procedure.
Outputs
The DROP PROCEDURE command has the following output: Table A-9: DROP PROCEDURE Output Output DROP PROCEDURE ERROR: DROP PROCEDURE: permission denied ERROR: RemoveFunction: function 'NAME(ARGS)' does not exist Description The message that the system returns if the command is successful. The user does not have Drop permission. For more information about permissions, see Managing User Account Permissions on page 3-1. The message which indicates that the specified procedure signature does not exist in the database.
ERROR: Name: No such stored The message that the system returns if the specified procedure stored procedure does not exist in the current database. ERROR: RemoveFunction: existing UDX NAME(ARGS) differs in size of string/numeric arguments This error indicates that a stored procedure exists with the name but has different sizes specified for string or numeric arguments. To drop the stored procedure, make sure that you specify the exact argument type list with correct sizes.
20470-03
Rev.2
A-9
Description
You cannot drop a stored procedure that is currently in use in an active query. After the active querys transaction completes, the Netezza system will process the DROP PROCEDURE command. The stored procedure must be defined in the current database. The DROP PROCEDURE command has the following characteristics:
Privileges Required
To drop a stored procedure, you must meet one of the following criteria:

You must have the Drop privilege on the PROCEDURE object. You must have the Drop privilege on the specific stored procedure. You must own the stored procedure. You must be the database admin user or own the current database.
Common Tasks
Use the DROP PROCEDURE command to drop an existing stored procedure from a database.
Related Commands
See ALTER PROCEDURE on page A-1 to alter stored procedures. See CALL and EXEC[UTE [PROCEDURE]] on page A-4 to invoke procedures. See CREATE [OR REPLACE] PROCEDURE on page A-6 to create procedures. See SHOW PROCEDURE on page A-10 to display information about the procedure.
Usage
To drop a sample stored procedure named mycalc, enter:

system(admin)=> DROP PROCEDURE mycalc();
SHOW PROCEDURE
Use the SHOW PROCEDURE command to display information about one or more stored procedures. The command checks your user account privileges to ensure that you are permitted to see information about the procedures defined in the database.
Synopsis
Syntax:
SHOW PROCEDURE [ALL | <name>] [VERBOSE]
A-10
20470-03
Rev.2
SHOW PROCEDURE
Inputs
The SHOW PROCEDURE command takes the following inputs: Table A-10: SHOW PROCEDURE Input Input ALL name Description Show information about all the stored procedures defined in the database. This is the default. Show information about one or more stored procedures defined in the database. You can specify a partial name. The command displays information for all the procedures with names that begin with the specified characters. You cannot specify a full signature. Display detailed information about the stored procedure.
VERBOSE
Outputs
The SHOW PROCEDURE command has the following output: Table A-11: SHOW PROCEDURE Output Output error found "(" (at char num) syntax error, unexpected '(', expecting $end Description The message that the system returns if you specify a full signature, for example:
show procedure returntwo();
Description
The SHOW PROCEDURE command has the following characteristics:
Privileges Required
Any user can run the SHOW PROCEDURE command. To see information about procedures in the output, you must be the admin user, own one or more procedures, own the current database, or have object privileges (such as Execute, List, Alter, or Drop) on one or more procedures or the Procedure object class.
Common Tasks
Use the SHOW PROCEDURE command to display one or all stored procedures in a database.
Related Commands
See ALTER PROCEDURE on page A-1 to alter stored procedures. See CALL and EXEC[UTE [PROCEDURE]] on page A-4 to invoke procedures. See CREATE [OR REPLACE] PROCEDURE on page A-6 to create procedures. See DROP PROCEDURE on page A-9 to drop a stored procedure.
20470-03
Rev.2
A-11
Usage
To show the sample stored procedure named returntwo, use the following command:
MYDB(ADMIN)=> SHOW PROCEDURE returntwo; RESULT | PROCEDURE | BUILTIN | ARGUMENTS --------------------------+-----------+---------+------------REFTABLE(MYDB.USER.TBL) | RETURNTWO | f | (TIMESTAMP) (1 row)
To show verbose information for the sample stored procedure named returntwo, use the following command. (Note that the sample output below is formatted to fit the page area.)
DEV(ADMIN)=> SHOW PROCEDURE returntwo VERBOSE; RESULT | PROCEDURE | BUILTIN | ARGUMENTS | OWNER | EXECUTEDASOWNER | VARARGS | DESCRIPTION | PROCEDURESOURCE -------------------------+-----------+---------+-------------+-------+-----------------+ --------+--------------+----------------REFTABLE(MYDB.USER.TBL) | RETURNTWO | f | (TIMESTAMP) | USER | t | f |This is a sample stored procedure | DECLARE BEGIN EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (1,1)'; EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (2,2)'; RETURN REFTABLE; END; (1 row)
If the stored procedure is obfuscated, the VERBOSE mode shows the obfuscated body text, as follows:
DEV(ADMIN)=> SHOW PROCEDURE customer VERBOSE; RESULT | PROCEDURE | BUILTIN | ARGUMENTS | OWNER | EXECUTEDASOWNER | VARARGS | DESCRIPTION | PROCEDURESOURCE -------------------------+-----------+---------+-------------+-------+-----------------+ --------+--------------+----------------BIGINT | CUSTOMER | f | () | USR | t | f | | TlpQU1FMV1JBUDEwVE5jZlh5TnpYbndCNkV5VFFMRTBiQT09JGE5N2p6ZEdJSVZwTTRrWmRRM0I3 WmUxZERZeWd6YkdjTWkxTzQrL1dCMmpqRGQvak9lUzFQQjArNGdlM08yZVdxUjRIMTFaTnROUmwKdk5xSm0wb1RPZz 09 (1 row)
To list all the stored procedures in a database, enter:
DEV(ADMIN)=> SHOW PROCEDURE ALL; RESULT | PROCEDURE | BUILTIN | ARGUMENTS ----------------------------+------------------+---------+-----------------------BOOLEAN | BOOLPROC | f | (BOOLEAN) CHARACTER | CHARPROC | f | (CHARACTER(ANY)) CHARACTER | CHARPROCANY | f | (CHARACTER(ANY)) CHARACTER | CHARPROCANY2 | f | (CHARACTER(10)) REAL | FLOAT4PROC | f | (REAL) DOUBLE PRECISION | FLOAT8PROC | f | (DOUBLE PRECISION) BYTEINT | INT1PROC | f | (BYTEINT)
A-12
20470-03
Rev.2
APPENDIX
Stored Procedure Examples
Whats in this appendix
Example of Simulating an Anonymous Block
This appendix contains examples of stored procedure definitions.
Variable Argument Stored Procedure

The following is an example of a stored procedure that uses variable arguments and the $var value to obtain the data type of the input parameters:
CREATE OR REPLACE PROCEDURE sp_varargs01(varargs) RETURNS INT4 LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE num_args int4; typ oid; idx int4; BEGIN num_args := PROC_ARGUMENT_TYPES.count; RAISE NOTICE 'Number of arguments is %', num_args; for i IN 0 .. PROC_ARGUMENT_TYPES.count - 1 LOOP typ := PROC_ARGUMENT_TYPES(i); idx := i+1; RAISE NOTICE 'argument $% is type % value ''%''', $idx; END LOOP; END; END_PROC;
idx, typ,
A sample call to the sp_varargs01 procedure follows:

select sp_varargs01(true, 'test'::char(10), 62443234::int8, 123::int2, 123456::int4, 34343.4343::float4, 1212.2323::float8, 'test2'::varchar(10), '2009-05-12'::date, '13:14:05'::time, '2009-0512 08:10:10'::timestamp, '2 days 1 hour'::interval, '11:40:36+05'::timetz, 3243.4324234::numeric, 3243.4324234::numeric(7,2), 5::int1, 'foo'::nchar(20), 'foo'::nvarchar(20), null::int4); NOTICE: Number of arguments is 19 NOTICE: argument $1 is type 16 value 't' NOTICE: argument $2 is type 1042 value 'test '
B-1
NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument NOTICE: argument sp_varargs01 -------------(1 row)
$3 is type 20 value '62443234' $4 is type 21 value '123' $5 is type 23 value '123456' $6 is type 700 value '34343.4' $7 is type 701 value '1212.2323' $8 is type 1043 value 'test2' $9 is type 1082 value '2009-05-12' $10 is type 1083 value '13:14:05' $11 is type 1184 value '2009-05-12 08:10:10' $12 is type 1186 value '2 days 01:00:00' $13 is type 1266 value '11:40:36+05' $14 is type 1700 value '3243.4324234' $15 is type 1700 value '3243.43' $16 is type 2500 value '5' $17 is type 2522 value 'foo ' $18 is type 2530 value 'foo' $19 is type 23 value '<NULL>'

The following is an NZPLSQL procedure that can execute anonymous blocks:
CREATE OR REPLACE PROCEDURE exec_nzplsql_block(text) RETURNS BOOLEAN LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE lRet BOOLEAN; DECLARE sid INTEGER; DECLARE nm varchar; DECLARE cr varchar; BEGIN sid := current_sid; nm := 'any_block' || sid || '()'; cr = 'CREATE OR REPLACE PROCEDURE ' || nm || ' RETURNS BOOL LANGUAGE NZPLSQL AS BEGIN_PROC ' || $1 || ' END_PROC'; EXECUTE IMMEDIATE cr; EXECUTE IMMEDIATE 'SELECT ' || nm; EXECUTE IMMEDIATE 'DROP PROCEDURE ' || nm; RETURN TRUE; END; END_PROC;
An example call and output follows:

DEV(ADMIN)=> call exec_nzplsql_block(BEGIN_PROC DECLARE var char; BEGIN var:= 'test'; raise NOTICE 'This is a %', var; END; END_PROC); NOTICE: This is a test EXEC_NZPLSQL_BLOCK -------------------t (1 row)
B-2
20470-03
Rev.2
20470-03
Rev.2
B-3
B-4
20470-03
Rev.2
APPENDIX
Notices and Trademarks
Whats in this appendix
Notices Trademarks Open Source Notifications Electronic Emission Notices Regulatory and Compliance
This section describes some important notices, trademarks, and compliance information.
Notices
This information was developed for products and services offered in the U.S.A. IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify the operation of any non-IBM product, program, or service. IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not grant you any license to these patents. You can send license inquiries, in writing, to: This information was developed for products and services offered in the U.S.A. IBM Director of Licensing IBM Corporation North Castle Drive Armonk, NY 10504-1785 U.S.A. For license inquiries regarding double-byte (DBCS) information, contact the IBM Intellectual Property Department in your country or send inquiries, in writing, to: IBM World Trade Asia Corporation Licensing 2-31 Roppongi 3-chome, Minato-ku Tokyo 106-0032, Japan The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY
C-1
KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this statement may not apply to you. This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these changes will be incorporated in new editions of the publication. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this publication at any time without notice. Any references in this information to non-IBM Web sites are provided for convenience only and do not in any manner serve as an endorsement of those Web sites. The materials at those Web sites are not part of the materials for this IBM product and use of those Web sites is at your own risk. IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you. Licensees of this program who wish to have information about it for the purpose of enabling: (i) the exchange of information between independently created programs and other programs (including this one) and (ii) the mutual use of the information which has been exchanged, should contact: IBM Corporation Software Interoperability Coordinator, Department 49XA 3605 Highway 52 N Rochester, MN 55901 U.S.A. Such information may be available, subject to appropriate terms and conditions, including in some cases, payment of a fee. The licensed program described in this document and all licensed material available for it are provided by IBM under terms of the IBM Customer Agreement, IBM International Program License Agreement or any equivalent agreement between us. Any performance data contained herein was determined in a controlled environment. Therefore, the results obtained in other operating environments may vary significantly. Some measurements may have been made on development-level systems and there is no guarantee that these measurements will be the same on generally available systems. Furthermore, some measurements may have been estimated through extrapolation. Actual results may vary. Users of this document should verify the applicable data for their specific environment. Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products. All statements regarding IBM's future direction or intent are subject to change or withdrawal without notice, and represent goals and objectives only. All IBM prices shown are IBM's suggested retail prices, are current and are subject to change without notice. Dealer prices may vary.
C-2
20470-03
Rev.2
Trademarks
This information contains examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental. COPYRIGHT LICENSE: This information contains sample application programs in source language, which illustrate programming techniques on various operating platforms. You may copy, modify, and distribute these sample programs in any form without payment to IBM, for the purposes of developing, using, marketing or distributing application programs conforming to the application programming interface for the operating platform for which the sample programs are written. These examples have not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of these programs. Each copy or any portion of these sample programs or any derivative work, must include a copyright notice as follows: your company name) (year). Portions of this code are derived from IBM Corp. Sample Programs. Copyright IBM Corp. _enter the year or years_. If you are viewing this information softcopy, the photographs and color illustrations may not appear.
Trademarks
IBM, the IBM logo, ibm.com and Netezza are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both. If these and other IBM trademarked terms are marked on their first occurrence in this information with a trademark symbol ( or ), these symbols indicate U.S. registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks is available on the Web at Copyright and trademark information at ibm.com/legal/copytrade.shtml. Adobe is a registered trademark of Adobe Systems Incorporated in the United States, and/ or other countries. Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both. Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both. NEC is a registered trademark of NEC Corporation. UNIX is a registered trademark of The Open Group in the United States and other countries. Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both. Red Hat is a trademark or registered trademark of Red Hat, Inc. in the United States and/or other countries. D-CC, D-C++, Diab+, FastJ, pSOS+, SingleStep, Tornado, VxWorks, Wind River, and the Wind River logo are trademarks, registered trademarks, or service marks of Wind River Systems, Inc. Tornado patent pending.
20470-03
Rev.2
C-3
APC and the APC logo are trademarks or registered trademarks of American Power Conversion Corporation. Other company, product or service names may be trademarks or service marks of others.
Open Source Notifications

PostgreSQL Portions of this publication were derived from PostgreSQL documentation. For those portions of the documentation that were derived originally from PostgreSQL documentation, and only for those portions, the following applies: PostgreSQL is copyright 1996-2001 by the PostgreSQL global development group and is distributed under the terms of the license of the University of California below. Postgres95 is copyright 1994-5 by the Regents of the University of California. Permission to use, copy, modify, and distribute this documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. In no event shall the University of California be liable to any party for direct, indirect, special, incidental, or consequential damages, including lost profits, arising out of the use of this documentation, even if the University of California has been advised of the possibility of such damage. The University of California specifically disclaims any warranties, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The documentation provided hereunder is on an "as-is" basis, and the University of California has no obligations to provide maintenance, support, updates, enhancements, or modifications. ICU Library The Netezza implementation of the ICU library is an adaptation of an open source library Copyright (c) 1995-2003 International Business Machines Corporation and others. ICU License - ICU 1.8.1 and later COPYRIGHT AND PERMISSION NOTICE Copyright (c) 1995-2003 International Business Machines Corporation and others All rights reserved. Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, provided that the above copyright notice(s) and this permission notice appear in all copies of the Software and that both the above copyright notice(s) and this permission notice appear in supporting documentation. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF THIRD PARTY RIGHTS. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR HOLDERS INCLUDED IN THIS NOTICE BE LIABLE FOR ANY CLAIM, OR ANY SPECIAL INDIRECT OR CONSEQUENTIAL DAMAGES, OR ANY DAMAGES WHATSOEVER RESULTING FROM
C-4
20470-03
Rev.2
Open Source Notifications
LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. Except as contained in this notice, the name of a copyright holder shall not be used in advertising or otherwise to promote the sale, use or other dealings in this Software without prior written authorization of the copyright holder. ODBC Driver The Netezza implementation of the ODBC driver is an adaptation of an open source driver, Copyright 2000, 2001, Great Bridge LLC. The source code for this driver and the object code of any Netezza software that links with it are available upon request to sourcerequest@netezza.com Botan License Copyright (C) 1999-2008 Jack Lloyd 2001 Peter J Jones 2004-2007 Justin Karneges 2005 Matthew Gregan 2005-2006 Matt Johnston 2006 Luca Piccarreta 2007 Yves Jerschow 2007-2008 FlexSecure GmbH 2007-2008 Technische Universitat Darmstadt 2007-2008 Falko Strenzke 2007-2008 Martin Doering 2007 Manuel Hartl 2007 Christoph Ludwig 2007 Patrick Sona All rights reserved. Redistribution and use in source and binary forms, for any use, with or without modification, of Botan (http://botan.randombit.net/license.html) is permitted provided that the following conditions are met: 1. Redistributions of source code must retain the above copyright notice, this list of conditions, and the following disclaimer. 2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions, and the following disclaimer in the documentation and/or other materials provided with the distribution. THIS SOFTWARE IS PROVIDED BY THE AUTHOR(S) "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR(S) OR CONTRIBUTOR(S) BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITYOF SUCH DAMAGE.
20470-03
Rev.2
C-5
Electronic Emission Notices

When you attach a monitor to the equipment, you must use the designated monitor cable and any interference suppression devices that are supplied with the monitor. Federal Communications Commission (FCC) Statement Note: This equipment has been tested and found to comply with the limits for a Class A digital device, pursuant to Part 15 of the FCC Rules. These limits are designed to provide reasonable protection against harmful interference when the equipment is operated in a commercial environment. This equipment generates, uses, and can radiate radio frequency energy and, if not installed and used in accordance with the instruction manual, may cause harmful interference to radio communications. Operation of this equipment in a residential area is likely to cause harmful interference, in which case the user will be required to correct the interference at his own expense. Properly shielded and grounded cables and connectors must be used in order to meet FCC emission limits. IBM is not responsible for any radio or television interference caused by using other than recommended cables and connectors or by unauthorized changes or modifications to this equipment. Unauthorized changes or modifications could void the user's authority to operate the equipment. This device complies with Part 15 of the FCC Rules. Operation is subject to the following two conditions: (1) this device may not cause harmful interference, and (2) this device must accept any interference received, including interference that might cause undesired operation. Industry Canada Class A Emission Compliance Statement This Class A digital apparatus complies with Canadian ICES-003. Avis de conformit la rglementation d'Industrie Canada Cet appareil numrique de la classe A est conforme la norme NMB-003 du Canada. Australia and New Zealand Class A Statement Attention: This is a Class A product. In a domestic environment this product may cause radio interference in which case the user may be required to take adequate measures. European Union EMC Directive Conformance Statement This product is in conformity with the protection requirements of EU Council Directive 2004/108/EC on the approximation of the laws of the Member States relating to electromagnetic compatibility. IBM cannot accept responsibility for any failure to satisfy the protection requirements resulting from a nonrecommended modification of the product, including the fitting of non-IBM option cards. Attention: This is an EN 55022 Class A product. In a domestic environment this product may cause radio interference in which case the user may be required to take adequate measures. Responsible manufacturer: International Business Machines Corp. New Orchard Road Armonk, New York 10504 914-499-1900
C-6
20470-03
Rev.2
Electronic Emission Notices
European Community contact: IBM Technical Regulations, Department M456 IBM-Allee 1, 71137 Ehningen, Germany Telephone: +49 7032 15-2937 Email: tjahn@de.ibm.com Germany Class A Statement Deutschsprachiger EU Hinweis: Hinweis fr Gerte der Klasse A EU-Richtlinie zur Elektromagnetischen Vertrglichkeit Dieses Produkt entspricht den Schutzanforderungen der EU-Richtlinie 2004/108/EG zur Angleichung der Rechtsvorschriften ber die elektromagnetische Vertrglichkeit in den EUMitgliedsstaaten und hlt die Grenzwerte der EN 55022 Klasse A ein. Um dieses sicherzustellen, sind die Gerte wie in den Handbchern beschrieben zu installieren und zu betreiben. Des Weiteren drfen auch nur von der IBM empfohlene Kabel angeschlossen werden. IBM bernimmt keine Verantwortung fr die Einhaltung der Schutzanforderungen, wenn das Produkt ohne Zustimmung der IBM verndert bzw. wenn Erweiterungskomponenten von Fremdherstellern ohne Empfehlung der IBM gesteckt/eingebaut werden. EN 55022 Klasse A Gerte mssen mit folgendem Warnhinweis versehen werden: Warnung: Dieses ist eine Einrichtung der Klasse A. Diese Einrichtung kann im Wohnbereich Funk-Strungen verursachen; in diesem Fall kann vom Betreiber verlangt werden, angemessene Manahmen zu ergreifen und dafr aufzukommen. Deutschland: Einhaltung des Gesetzes ber die elektromagnetische Vertrglichkeit von Gerten Dieses Produkt entspricht dem Gesetz ber die elektromagnetische Vertrglichkeit von Gerten (EMVG). Dies ist die Umsetzung der EU-Richtlinie 2004/108/EG in der Bundesrepublik Deutschland. Zulassungsbescheinigung laut dem Deutschen Gesetz ber die elektromagnetische Vertrglichkeit von Gerten (EMVG) (bzw. der EMC EG Richtlinie 2004/108/EG) fr Gerte der Klasse A Dieses Gert ist berechtigt, in bereinstimmung mit dem Deutschen EMVG das EG-Konformittszeichen - CE - zu fhren. Verantwortlich fr die Einhaltung der EMV Vorschriften ist der Hersteller: International Business Machines Corp. New Orchard Road Armonk, New York 10504 914-499-1900 Der verantwortliche Ansprechpartner des Herstellers in der EU ist: IBM Deutschland Technical Regulations, Department M456 IBM-Allee 1, 71137 Ehningen, Germany Telephone: +49 7032 15-2937 Email: tjahn@de.ibm.com Generelle Informationen: Das Gert erfllt die Schutzanforderungen nach EN 55024 und EN 55022 Klasse A.
20470-03
Rev.2
C-7
Japan VCCI Class A Statement
This is a Class A product based on the standard of the Voluntary Control Council for Interference (VCCI). If this equipment is used in a domestic environment, radio interference may occur, in which case the user may be required to take corrective actions. Japan Electronics and Information Technology Industries Association (JEITA) Statement
Japan Electronics and Information Technology Industries Association (JEITA) Confirmed Harmonics Guidelines (products less than or equal to 20 A per phase) Japan Electronics and Information Technology Industries Association (JEITA) Statement
Japan Electronics and Information Technology Industries Association (JEITA) Confirmed Harmonics Guidelines (products greater than 20 A per phase) Korea Communications Commission (KCC) Statement
This is electromagnetic wave compatibility equipment for business (Type A). Sellers and users need to pay attention to it. This is for any areas other than home. Russia Electromagnetic Interference (EMI) Class A Statement
People's Republic of China Class A Electronic Emission Statement
C-8
20470-03
Rev.2
Regulatory and Compliance
Taiwan Class A Compliance Statement
Regulatory and Compliance

Regulatory Notices Install the NPS system in a restricted-access location. Ensure that only those trained to operate or service the equipment have physical access to it. Install each AC power outlet near the NPS rack that plugs into it, and keep it freely accessible. Provide approved circuit breakers on all power sources. Product may be powered by redundant power sources. Disconnect ALL power sources before servicing. High leakage current. Earth connection essential before connecting supply. Courant de fuite lev. Raccordement la terre indispensable avant le raccordement au rseau. Homologation Statement Attention: This product is not intended to be connected directly or indirectly by any means whatsoever to interfaces of public telecommunications networks, neither to be used in a Public Services Network. WEEE Netezza Corporation is committed to meeting the requirements of the European Union (EU) Waste Electrical and Electronic Equipment (WEEE) Directive. This Directive requires producers of electrical and electronic equipment to finance the takeback, for reuse or recycling, of their products placed on the EU market after August 13, 2005.
20470-03
Rev.2
C-9
C-10
20470-03
Rev.2
Index
Index
Symbols
$var variable 2-4 %, used in message formats 2-21
D
data types 2-6 DEBUG messages 2-21 declarations section, in NZPLSQL 2-2 DEFAULT clause, variables 2-3 disk space problems, avoiding for large datasets 2-25 double dash comments 2-2 downgrade cautions 1-7 DROP PROCEDURE command A-9 using 3-11 dropping when database is dropped 1-7 dynamic queries 2-15
A
account permissions, managing 3-1 admin user, permissions 3-1 aliases, for data types 2-6 ALTER PROCEDURE command A-1 using 3-10 ANY keyword 3-6 ANY keyword, for procedure input arguments 3-5 arithmetic evaluations, and stored procedures 2-10 array variables assigning a value 2-9 in NZPLSQL 2-9 reference support 2-9 assignment statement 2-14
E
ELSE IF statement 2-17 ELSIF statement 2-17 END_PROC keyword 1-5 error handling, in NZPLSQL 2-22 errors, raising 2-21 EXCEPTION messages 2-21 EXCEPTION statement 2-22 EXEC command, using 3-10 execute as caller permissions 1-4 execute as owner permissions 1-4 EXECUTE command A-4 using 3-10 EXECUTE IMMEDIATE statement 2-15 EXECUTE PROCEDURE command, using 3-10 EXIT statement 2-18 expressions in NZPLSQL 2-9 string processing best practices 2-15 EXTEND method, array variables 2-9
B
backups, and stored procedures 1-7 BEGIN_PROC keyword 1-5 best practices, stored procedures 1-4 block comments 2-2 block quoting support 1-5 block structured language 2-1 block, in NZPLSQL 2-1
C
CALL command discarding results 2-14 example 3-10 usage A-4 call recursion limit 2-15 casting impact on stored procedures 2-11 input values to match signature sizes 3-6 COMMENT ON PROCEDURE command, using 3-11 comments best practices 3-11 in NZPLSQL 2-2 conditional control 2-16 CONSTANT option, variables 2-3 constants, in NZPLSQL 2-2 control statements conditional 2-16 iterative 2-17 control structures 2-16 COUNT method, array variables 2-9 CREATE OR REPLACE PROCEDURE command A-6 generic arguments example 3-7 using 3-3 cross-database access, to stored procedures 1-4
F
FOR IN loop statement 2-20 FOR loop, iteration step count 2-19 FOR statement 2-19 FOR-IN EXECUTE statement 2-21 fully-qualified object names, for stored procedures 1-4
G
generic arguments, benefits of 3-5 generic procedures ANY keyword 3-6 input arguments 3-6 registering 3-7 generic return value 3-7 GRANT ALL command, create permission 3-2 GRANT command alter permission 3-2 create permission 3-2 drop permission 3-3 execute permission 3-2
Index-1
Index
H
hiding the procedure body 3-7
records 2-19 statements 2-14 variable datatypes 2-2 variables 2-2
I
IF statements 2-16 IF-THEN statements 2-16 IF-THEN-ELSE IF statement 2-17 IF-THEN-ELSE statements 2-16 implicit casting for procedure input values 3-6 in assignments 2-14 in stored procedures, best practices 2-10 infinite loop, handling 2-26 iterative control 2-17
O
obfuscating procedures using in registration scripts 3-9 using wrap_nzplsql 3-7 outputs ALTER PROCEDURE command A-2 CREATE OR REPLACE PROCEDURE command A-7 DROP PROCEDURE command A-9 SHOW PROCEDURE command A-11 overflows, avoiding in stored procedures 2-11 overloading procedures 3-4 owner, stored procedure 3-1
L
labels, used to identify variables 2-5 large datasets, managing 2-25 LAST_OID variable 2-15 loop processing 2-26 LOOP statement 2-17 loops infinite, handling 2-26 iterate over integer count 2-19 repeating while true 2-18 terminating 2-18 unconditional 2-17
P
parameters 2-4 patches, and stored procedures 1-7 PATH SQL session variable 1-4 permissions granting all 3-2 alter permission 3-2 create 3-2 drop permission 3-3 execute permission 3-2 managing 3-1 revoking alter permission 3-2 create permission 3-2 drop permission 3-3 execute permission 3-2 PL/pgSQL language 1-2 privileges, commands ALTER PROCEDURE command A-3 CALL or EXECUTE PROCEDURE command A-5 CREATE PROCEDURE command A-8 privileges, managing for accounts 3-1 PROC_ARGUMENT_TYPES array 2-4 procedural programming language 1-1 procedures calling 2-14 obfuscating 3-7 returning from 2-16
M
massively parallel processing (MPP), designing procedures for 2-26 message levels, types of 2-21 messages, reporting 2-21
N
Netezza SQL commands for stored procedures 1-2 reference A-1 NOT NULL option, variables 2-3 NOTICE messages 2-21 NzAdmin administrative interface, for stored procedures 3-12 NZPLSQL language about 1-2 array variables 2-9 comments 2-2 constants 2-2 control structures 2-16 description of 2-1 dynamic queries 2-15 exception support 2-22 expressions 2-9 message and error reporting 2-21 procedural logic 1-2 record assignments 2-19 record variable 2-19
Q
quote_ident function 2-15 quote_literal function 2-15
R
RAISE statement 2-21 RECORD variable 2-19 records about 2-19
Index-2
Index
assignments 2-19 recursion limit, calls 2-15 registration script, with obfuscated procedures 3-9 restores, and stored procedures 1-7 RETURN command 2-16 return value, generic 3-7 REVOKE command alter permission 3-2 drop permission 3-3 ROW_COUNT variable 2-15 %ROWTYPE attribute 2-3
See also procedures. showing A-10 signature 3-4 user of 3-1 strings, processing in expressions 2-15
T
TRIM method, array variables 2-9 %TYPE attribute 2-3
S
scoping, variables 2-5 SHOW PROCEDURE command A-10 using 3-12 signature about 3-4 used in permissions 3-1 SQL commands, for stored procedures A-1 SQLERRM variable 2-22 statements assignment 2-14 in NZPLSQL 2-14 section, in NZPLSQL 2-2 stored procedures 1-4, 1-7 about 1-1 account permission overview 3-1 altering 3-10, A-1 benefits of 1-2 best practices 1-4 block quoting support 1-5 call recursion limit 2-15 calling, executing 3-10 commenting on 3-11 creating 1-3, 3-3 creating or replacing A-6 designing 3-3 displaying information for 3-12 dropping 3-11, A-9 example use of 1-1 executing 1-3 fully qualified name of 1-4 generic, registering 3-7 impact of implicit casting 2-11 input 1-3 invoking CALL A-4 using EXECUTE A-4 limitations of size-specific input arguments 3-5 loop processing 2-26 managing 3-1 math best practices 2-10 obfuscating the body 3-7 owner of 3-1 PATH session variable 1-4 process to create 3-3 result sets 1-3 return types 1-3 security and access controls 1-2 security considerations 1-4
U
unconditional loop 2-17 upgrades, and stored procedures 1-7
V
VARARGS keyword 2-4, 3-7 variable arguments, benefits of 3-7 variable scoping 2-5 variables datatypes 2-2 in NZPLSQL 2-2
W
WHILE statement 2-18 wrap_nzplsql built-in procedure 3-8
Index-3
Index
Index-4

Netezza Stored Procedures Guide

Uploaded by

Copyright:

Available Formats

Netezza Stored Procedures Guide

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Netezza Stored Procedures Guide

Uploaded by

Copyright:

Available Formats

What are stored procedures and how are they used?

What are stored procedures and how are they used?

What language is used to write stored procedures?

What language is used to write stored procedures?

IBM Netezza 6.0.

IBM Netezza Stored Procedures Developers Guide

2 NZPLSQL Statements and Grammar

3 Creating and Managing Stored Procedures

Appendix A: SQL Reference

Appendix B: Stored Procedure Examples

Appendix C: Notices and Trademarks

About This Guide

Stored Procedure Examples on page B-1

The Purpose of This Guide

Symbols and Conventions

If You Need Help

Comments on the Documentation

We appreciate your comments on the documentation.

IBM Netezza Stored Procedures Developers Guide

SQL Commands for Stored Procedures

How to Create and Use a Stored Procedure

Stored Procedures Input and Return Types

How to Create and Use a Stored Procedure

How to Execute a Stored Procedure

IBM Netezza Stored Procedures Developers Guide

Best Practices for Developers

Cross-Database Access to Stored Procedures

To display the PATH value, use the following command:

Best Practices for Developers

MYDB(ADMIN)=> SELECT CURRENT_PATH; CURRENT_PATH -----------------MYDB,NZDB,CUSTOMER (1 row)

Block Quoting Support

IBM Netezza Stored Procedures Developers Guide

SQL Restrictions within the Stored Procedure

Best Practices for Netezza Administrators

Best Practices for Netezza Administrators

Backing Up and Restoring Stored Procedures

Upgrading and Patching Netezza Systems that Have Stored Procedures

Downgrading Netezza Systems with Stored Procedures

IBM Netezza Stored Procedures Developers Guide

IBM Netezza Stored Procedures Developers Guide

Variables and Constants

A variable declaration has the following syntax:

Variables and Constants

IBM Netezza Stored Procedures Developers Guide

Argument List and Variables

Variables and Constants

A sample call follows:

IBM Netezza Stored Procedures Developers Guide

Sample output for this stored procedure follows:

Data Types and Aliases

Variables and Constants

IBM Netezza Stored Procedures Developers Guide

TIME WITH TIME ZONE

NUMERIC, DEC, DEC(p,s), DECIMAL(p,s), DECIMAL

DOUBLE, FLOAT, FLOAT(p), FLOAT8

IBM Netezza Stored Procedures Developers Guide

The second example follows:

Literals and Math Operations in Expressions

IBM Netezza Stored Procedures Developers Guide

Another example follows: