Wednesday, July 13, 2011

PL/pgSQL

PL/pgSQL Block Structure
  • DECLARE (Optional) 
    • Variables, 
    • cursors,
  • BEGIN (Mandatory) 
    • SQL statements 
    • PLPGSQL statements
  • EXCEPTION (Optional) 
    • Actions to perform when errors occur
  • END; (Mandatory)
  • All key words and identifiers can be written in mixed upper and lower case. Identifiers are implicitly converted to lowercase unless doublequoted.
  • There are two types of comments in PL/pgSQL.
    • -- starts a comment that extends to the end of the line.
    • /* multi-line comments */

Tuesday, July 12, 2011

Procedural Languages

Procedural Languages Overview
  • PostgreSQL allows user-defined functions to be written in a variety of procedural languages. 
  • The database server has no built-in knowledge about how to interpret the function's source text. 
  • Instead, the task is passed to a handler that knows the details of that particular language.
  • PostgreSQL currently supports several standard procedural languages :
    • PL/pgSQL
    • PL/Tcl
    • PL/Perl
    • PL/Python
    • PL/Java
    • PL/Ruby
    • Other languages can be defined by users
  • PL/pgSQL is a loadable procedural language for the PostgreSQL database system.
  • PL/pgSQL has several distinct features :
    • Can be used to create functions and trigger procedures,
    • Adds control structures to the SQL language,
    • Can perform complex computations,
    • Inherits all user-defined types, functions, and operators,
    • Can be defined to be trusted by the server,
    • Is easy to use.

Followers