PLpgSQL

I l @ ve RuBoard

PL/pgSQL

The PL/pgSQL language is the default language typically used to perform server-side programming. It combines the ease of SQL with the power of a scripting language.

With PL/pgSQL, it is possible to build custom functions, operators, and triggers. A standard use might be to incorporate commonly called queries inside the database. Many RDBMSs refer to this as stored-procedures, and it offers a way for client applications to quickly request specific database services without the need for a lengthy communication transaction to occur. The overhead involved in establishing a conversation between a client and server machine can often significantly slow down the apparent speed of the system.

When a PL/pgSQL-based function is created, it is compiled internally as byte code. The resultant near-binary code is then executed each time the function is called. PostgreSQL will execute the PL/pgSQL compiled code rather than having to reinterpret individual SQL commands. Therefore, this can result in a significant performance increase compared to reissuing the same SQL commands time after time.

Another benefit of using PL/pgSQL is when portability is an issue. Because PL/pgSQL is executed entirely within the PostgreSQL system, this means that PL/pgSQL code can be run on any system running PostgreSQL.

PL/pgSQL Language Specifics

The basic structure of PL/pgSQL code is as follows :

 <label declaration>  [DECLARE       Statements ]  BEGIN       Statements  END; 

Any number of these blocks can be encapsulated inside each other, for instance:

 <label declaration>  [DECLARE       Statements ]  BEGIN       [DECLARE            Statements ]       BEGIN            Statements       END;            Statements  END; 

When PostgreSQL encounters multiple groups of DECLARE BEGIN END statements, it interprets all variables as local to their respective group . In effect, variables used in one subgroup are not accessible to variables in neighboring or parent groups. For instance, in this example, all the myvar variables are local to their respective subgroups:

 CREATE FUNCTION myfunc() RETURNS INTEGER AS '  DECLARE       myvar INTEGER := 1;  BEGIN       RAISE NOTICE "My Variable is %", myvar;       DECLARE            myvar VARCHAR := "Hello World";       BEGIN            RAISE NOTICE "My Variable is %", myvar;       END;  END;' LANGUAGE 'plpgsql'; 

In this instance, not only do the two instances of the myvar variable contain different data, they are designated to hold different data types as well.

Comments

PL/pgSQL has two different comment styles: one for inline comments (such as --) and another for comment blocks (such as /* */ ). For instance:

 BEGIN       Some-code                  --this is a comment       <>       <>       <>       Some-more-code       <>       /* And this       is a comment       block */  END; 

Variable Assignment and Declaration

Variable declarations are made in the DECLARE block of the PL/pgSQL statement. Any valid SQL data type can be assigned to a PL/pgSQL variable. Declaration statements follow this syntax:

  name  [ CONSTANT ]  type  [ NOT NULL ] [ {DEFAULT  := }  value  ]  name  -     The name of the variable being defined.  CONSTANT - Keyword that indicates that this variable is read-only.  Type -     The SQL data type (e.g., INTEGER, INTERVAL, VARCHAR, etc.).  NOT NULL - By default, all variables are initialized as NULL unless a value is set.  Including this keyword will not allow a NULL value to be set to this variable, and  will mandate that either a default or explicit value is set.  DEFAULT -  Keyword that indicates that a default value is set.  value  -    The value of the default value or the explicit declaration. 

If a variable type is unknown, the programmer can make use of the %TYPE and %ROWTYPE commands, which will automatically gather a specific variable type or an entire row from a database table.

For instance, if you wanted to automatically type the variable myvar as the same type as the table/field payroll.salary , you could use the following:

 CREATE FUNCTION yearlysalary(INTEGER, INTEGER) RETURN INTEGER AS '       DECLARE            myvar payroll.salary%TYPE;       BEGIN            RETURN myvar*2;       END;       ' LANGUAGE 'plpgsql'; 

Alternatively, an entire database row can be typed by using the %ROWTYPE syntax. For instance:

 CREATE FUNCTION yearlysalary(INTEGER, INTEGER) RETURN INTEGER AS '       DECLARE            myvar payroll%TYPE;       BEGIN            RETURN myvar.salary*2;       END;       ' LANGUAGE 'plpgsql'; 

Passing Variables to Functions

PL/pgSQL can accommodate up to 16 passed variables. It refers to variables by their ordinal number.The numbering sequence starts at 1; therefore $1 represents the first variable passed, $2 the second, and so on.

There is no need to declare the data type of the passed variable; PL/pgSQL will automatically cast the appropriate variable number as the proper data type.

Using the ALIAS keyword, however, enables the programmer to alias a more descriptive variable name to the ordinal number. For instance:

 CREATE FUNCTION addnumbers(INTEGER, INTEGER) RETURN INTEGER AS '       DECLARE            Number_1 ALIAS FOR ;            Number_2 ALIAS FOR ;       BEGIN            RETURN Number_1 + Number_2;       END;       ' LANGUAGE 'plpgsql'; 

Additionally, the RENAME command can be used to rename current variables to alternate names . For instance:

 CREATE FUNCTION addnumbers(INTEGER, INTEGER) RETURN INTEGER AS '       DECLARE            Number_1 ALIAS FOR ;            Number_2 ALIAS FOR ;       BEGIN            RENAME Number_1 TO Orig_Number            RETURN Orig_Number + Number_2;       END;       ' LANGUAGE 'plpgsql'; 

Control Statements

PL/pgSQL supports most of the common control structures such as IF...THEN and WHILE loops , and FOR statements. Most of the syntax of these statements works as it does in other languages. The following sections outline the basic format expected by these control statements.

IF THEN ELSE ELSE IF

In addition to just the basic IF THEN statement, PL/pgSQL also provides the capability to perform ELSE and ELSE IF exception testing.A string of ELSE IF conditional tests is analogous to using a CASE or SWITCH statement, which is often found in other programming languages.

 IF conditional-expression THEN       execute-statement;  END IF;  IF conditional-expression THEN       execute-statement;  ELSE       execute-statement;  END IF;  IF conditional-expression THEN       execute-statement;  ELSE IF conditional-expression2 THEN       execute-statement;  END IF; 

LOOPS

Like all programming languages, PL/pgSQL includes the capability to create code loops that will only run when certain conditions are met. Loops can be particularly useful when traversing a series of rows in a table and performing some manipulation.

An endless loop could be created using the following template:

 LOOP       Statements;  END LOOP;  Such as:  LOOP       x:=x+1;  END LOOP; 

Or, alternatively, the EXIT directive can be used with an IF THEN statement to create an exit point.

 LOOP       x:=x+1;       IF x>10 THEN            EXIT;       END IF;  END LOOP; 

Another way of performing the preceding task is to use the EXIT WHEN statement, such as:

 LOOP       x:=x+1;       EXIT WHEN x>10;  END LOOP; 

The WHILE clause can be included to offer a cleaner implementation of the preceding, such as:

 WHILE x<10 LOOP       x:=x+1;  END LOOP; 

In contrast to a WHILE -type loop, a FOR loop is expected to perform a fixed number of iterations. The FOR statement expects the following syntax:

 FOR name IN [ REVERSE ] expression__start..expression_end LOOP 

For instance, the following two examples count from 1 to 100 and from 100 to 1, respectively:

 FOR a IN 1..100 LOOP       RAISE NOTICE '%', a;  END LOOP;  FOR a IN REVERSE 1..100 LOOP       RAISE NOTICE '%', a;  END LOOP; 

Although these examples are similar in functionality to the WHILE loops, the real power of using FOR loops is for traversing record sets. For instance, this example traverses through the payroll table and summarizes the total amount paid out for a given payroll period:

 CREATE FUNCTION totalpay(DATETIME) REAL AS '       DECLARE            recs RECORD;            payroll_period ALIAS ;            retval REAL :=0;       BEGIN            FOR recs IN SELECT * FROM PAYROLL WHERE                 payperiod=payroll_period LOOP                 retval:=retval+PAYROLL.SALARY;            END LOOP;            RETURN retval       END;       ' LANGUAGE 'plpgpsql'; 

Using SELECT

PL/pgSQL has some slight differences from standard SQL in how the SELECT statement operates inside of a code block. The SELECT INTO command normally creates a new table; inside of a PL/pgSQL code block, however, this declarative assigns the selected row to a variable placeholder. For instance, this example declares a variable myrecs as a RECORD and fills it with the output of a SELECT query.

 CREATE FUNCTION checkemail() RETURNS INTEGER AS '       DECLARE            myrecs RECORD;       BEGIN;            SELECT INTO myrecs * FROM authors WHERE                 name='Barry';            IF myrecs.email IS NULL THEN                 RETURN 0;            ELSE                 RETURN 1;            END IF;       END;       ' LANGUAGE 'plpgsql'; 

In the preceding example, the existence of an email is determined by comparing it against a SQL NULL value. Alternatively, the NOT FOUND clause can be used following a SELECT INTO query. For example:

 CREATE FUNCTION checkemail() RETURNS INTEGER AS '       DECLARE            myrecs RECORD;       BEGIN;            SELECT INTO myrecs * FROM authors WHERE                 name='Barry';            IF NOT FOUND THEN                 RETURN 0;            ELSE                 RETURN 1;            END IF;       END;       ' LANGUAGE 'plpgsql'; 

Executing Code Inside Functions

There are two basic methods for executing code within a current code block. If a return value is not required, the developer can call the code with the PERFORM command.

If dynamic queries are desired, the EXECUTE command can be used.

The following example gives an indication of how the PERFORM command would be used. First, a custom function is defined, addemp , which accepts the parameters needed to create an employee. If the employee already exists, however, the function exits with a 0 exit code. However, if the employee was created, the exit code is a 1. The following is an example of your first function:

 CREATE FUNCTION addemp(VARCHAR, INTEGER, INTEGER)  RETURNS INTEGER AS '        DECLARE              Name ALIAS FOR ;              EmpID ALIAS FOR ;              Age ALIAS FOR ;              EmpRec RECORD;        BEGIN              /* Check to see if emp exist */              SELECT INTO EmpRec * FROM employee WHERE                    Employee.emp_id=EmpID;              IF NOT FOUND THEN                    /* Doesn't exist, so add them  INSERT INTO employee VALUES (Name, EmpID, Age);                      RETURN 1;                 ELSE                      /* Emp already exist, exit status 0 */                      RETURN 0;                 END IF;            END;            ' LANGUAGE 'plpgsql'; 

After the preceding is created, you can now call this function from another by using the PERFORM statement. As mentioned earlier, the PERFORM statement ignores any return values from the called function. So, in this case, the returned 0 or 1 exit code will be ignored. However, due to the nature of how the addemp function is being used, that is not a concern.

 <function is created>       <Some Code>              /*Traverse List and run against addemp function */       FOR emp IN SELECT * FROM TempEmps;            PERFORM addemp(emps.name, emps.emp_id, emps.age);       END LOOP;              <Some Code>  <End Function> 

In the preceding case, no return values are processed from the PERFORM addemp clause. In this instance, this is a desired behavior because the addemp function will only add employees when it is appropriate to do so.

The EXECUTE statement contrasts with the PERFORM command in that, instead of executing predefined functions, the EXECUTE statement is designed to handle dynamic queries.

For instance, the following code snippet gives a brief example of how this could be used:

 CREATE FUNCTION orderemp(VARCHAR) RETURNS OPAQUE AS '        DECLARE              SortOrder ALIAS FOR ;              QueryStr VARCHAR;        BEGIN              /* Determine Sorting Order */              IF SortOrder := "Age" THEN                    QueryStr := "age";              ELSE IF SortOrder := "ID" THEN                    QueryStr := "emp_id";              ELSE IF SortOrder := "FName" THEN                    QueryStr := "first_name";              ELSE IF SortOrder := "LName" THEN                    QueryStr := "last_name";              ELSE  RAISE NOTICE "Unknown value: "  SortOrder;  RETURN 0;              END IF;  EXECUTE "SELECT * FROM employee ORDER BY "   QueryStr;              RETURN 1;        END IF;  ' LANGUAGE 'plpgsql'; 

The preceding example shows how a basic dynamic query can be created using the EXECUTE statement. However, much more complex uses are possible. In fact, it is possible to actually use the EXECUTE statement to create custom functions within other functions.

Exceptions and Notifications

PL/pgSQL uses the RAISE statement to insert messages into the PostgreSQL log system. The basic format for the RAISE command is as follows:

 RAISE  level  '  format  ' [,  identifier  []]; 

level ”Either DEBUG , NOTICE , or EXCEPTION .

format ”Uses the % character to denote the placeholder for the comma-separated list in identifier .

identifier ”The list (text strings and variables) of messages to log.

DEBUG will be silently ignored if debugging is turned off (compile-time option). NOTICE will write the message to the client application and enter it in the PostgreSQL system log file. EXCEPTION will perform all the actions of NOTICE and additionally force a ROLLBACK from the parent transaction.

The following are some examples:

 RAISE NOTICE "Warning! Salary change attempted by non-manager";  RAISE NOTICE "User % not found in payroll table", user_id;  RAISE EXCEPTION "Invalid Entry in Payroll Table..aborting"; 

Unfortunately, PL/pgSQL does not have built-in mechanisms for detecting or recovering from an error based on RAISE events. This can be done either by setting specific return variables or through explicit trapping done in the client application. However, in most cases ”particularly if the transaction is aborted ”not much can be done with regard to automatic recovery; usually human intervention will be required at some level.

Retrieving System Variables

PL/pgSQL also includes the capability for a function to retrieve certain diagnostic settings from the PostgreSQL back end while in process. GET DIAGNOSTICS can be used to retrieve the ROW_COUNT and the RESULT_OID . The syntax would be as follows:

 GET DIAGNOSTICS mycount = ROW_COUNT;  GET DIAGNOSTICS last_id = RESULT_OID; 

The RESULT_OID would only make sense after an insert was immediately performed previously in the code.

Notes

The BEGIN and END statements that define a PL/pgSQL code block are not analogous to the BEGIN END SQL transaction clause. The SQL BEGIN END statements define the start and commit of a transactional statement. A PL/pgSQL function is automatically part of either an explicit or implicit transaction in the SQL query that called it. Because PostgreSQL does not support nested transactions, it is not possible to have a transaction be part of a called function.

Just like with standard SQL declarations, in PL/pgSQL, arrays can be used by utilizing the standard notation (for example, myint INTEGER(5); ).

The main differences between PL/pgSQL and Oracle's procedural languages are that PostgreSQL can overload functions, CURSORS are not needed in PostgreSQL, default parameters are allowed in function calls in PostgreSQL, and PostgreSQL must escape single quotes. (Because the function itself is already in quotes, queries inside a function must use a series of quotes to remain at the proper level .) There are other differences, but most of these deal with specific syntax issues; consult an Oracle PL/SQL book for more information.

I l @ ve RuBoard