PL/pgSQL — SQL Procedural Language

PL/pgSQL is a loadable procedural language for the QHB database system. The design goals of PL/pgSQL were to create a loadable procedural language that

• can be used to create functions, procedures, and triggers,

• adds control structures to the SQL language,

• can perform complex computations,

• inherits all user-defined types, functions, procedures, and operators,

• can be defined to be trusted by the server,

• is easy to use.

Functions created with PL/pgSQL can be used anywhere that built-in functions could be used. For example, it is possible to create complex conditional computation functions and later use them to define operators or use them in index expressions.

In QHB PL/pgSQL is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it.

Table of Contents

• Overview
  • Advantages of Using PL/pgSQL
  • Supported Argument and Result Data Types
• Structure of PL/pgSQL
• Declarations
  • Declaring Function Parameters
  • ALIAS
  • Copying Types
  • Row Types
  • Record Types
  • Collation of PL/pgSQL Variables
• Expressions
• Basic Statements
  • Assignment
  • Executing SQL Commands
  • Executing a Command with a Single-Row Result
  • Executing Dynamic Commands
  • Obtaining the Result Status
  • Doing Nothing At All
• Control Structures
  • Returning from a Function
  • Returning from a Procedure
  • Calling a Procedure
  • Conditionals
  • Simple Loops
  • Looping through Query Results
  • Looping through Arrays
  • Trapping Errors
  • Obtaining Execution Location Information
• Cursors
  • Declaring Cursor Variables
  • Opening Cursors
  • Using Cursors
  • Looping through a Cursor's Result
• Transaction Management
• Errors and Messages
  • Reporting Errors and Messages
  • Checking Assertions
• Trigger Functions
  • Triggers on Data Changes
  • Triggers on Events
• PL/pgSQL under the Hood
  • Variable Substitution
  • Plan Caching
• Tips for Developing in PL/pgSQL
  • Handling of Quotation Marks
  • Additional Compile-Time and Run-Time Checks
• Porting from Oracle PL/SQL
  • Porting Examples
  • Other Things to Watch For
  • Appendix