Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

Statements and Expressions

PL/pgSQL code is composed of statements and expressions (as most programming languages are). Most of your code will be made of statements, and you will probably find yourself using expressions often, as they are essential to certain types of data manipulation. The concept of statements and expressions is generally applicable to all programming languages in alike (or at least very similar) ways, and if you have worked with programming languages before, you may already have a general understanding of them.


A statement performs an action within PL/pgSQL code, such as assignment of a value to a variable or the execution of a query. The organization of statements within a PL/pgSQL code block controls the order in which operations are executed within that code block. The bulk of your statements will be placed in the main operation section of a code block, which is located after the BEGIN keyword and before the END keyword. Some declarative statements should appear in the declaration section (after the DECLARE keyword), but these should only declare and/or initialize the variables that will be referenced within the code block.

Every statement should end with a semicolon character ( ;). This is similar to SQL, which also requires each statement to be ended with a semicolon. Types of statements (and their uses) are discussed throughout the rest of this chapter, as most everything you will do within PL/pgSQL will be done with statements.


Expressions are calculations or operations that return their results as one of PostgreSQL's base data types. An example expression is x := a + b, which adds the variables a and b, then assigns the result to the variable x. Example 11-8 shows a simple PL/pgSQL function that assigns the returned result of a multiplication expression to the variable x, and Example 11-9 shows the output when selecting the function in psql .

Example 11-8. Using expressions

CREATE FUNCTION a_function () RETURNS int4 AS '
   an_integer int4;
   an_integer := 10 * 10;
   return an_integer;
' LANGUAGE 'plpgsql';

Example 11-9. Output of a_ function( )

booktown=# SELECT a_function() AS output;
(1 row)

With the exception of dynamic queries (SQL queries run with the EXECUTE keyword), all PL/pgSQL expressions in a function are only prepared once during the lifetime of the PostgreSQL backend process. Since expressions are only prepared once, constant values (not constant variables, but values such as the now and current timestamp values) used in PL/pgSQL expressions are only prepared once, causing code with constant values that require run-time interpretation to break. Example 11-10 shows how to force PL/pgSQL to evaluate constant timestamp values at a function's run-time, instead of once per creation.

The add_shipment function in Example 11-10 is a fairly advanced function that uses techniques and aspects of the language covered later in this chapter. Essentially, add_shipment accepts a customer ID number and book ISBN, calculates the next shipment ID by adding one to the current highest shipment ID, then inserts the values with a now timestamp into the shipments table.

If we had used now directly in the INSERT INTO statement, the now string would have been cast into a timestamp at the time the function was created, and the timestamp created would be used in all future calls of the function.

Example 11-10. Using timestamp values correctly

CREATE FUNCTION add_shipment (integer, text) RETURNS timestamp AS '
     -- Declare aliases for function arguments.
    customer_id ALIAS FOR $1;
    isbn ALIAS FOR $2;
     -- Declare a variable to hold the shipment ID number and
     -- the current time.
    shipment_id INTEGER;
    right_now timestamp;
     -- Set the current time variable to the string ''now''.
    right_now := ''now'';
     -- Order the existing shipments by their ID numbers, beginning
     -- with the highest number, then insert the first ID number into
     -- the shipment_id variable.
    SELECT INTO shipment_id id FROM shipments ORDER BY id DESC;
     -- Add one to the shipment_id variable.
    shipment_id := shipment_id + 1;
     -- Insert a shipment record into the shipments table.  The
     -- right_now variable will be typecast to a timestamp at
     -- run-time, causing constant value now to be interpreted as
     -- the timestamp each time the function is run.
    INSERT INTO shipments VALUES ( shipment_id, customer_id, isbn, right_now );
     -- Return a timestamp using the constant value now.
    RETURN right_now;
' LANGUAGE 'plpgsql';
Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire