Expressions are calculations which return a resulting value. They can be used to enhance data manipulation. In some cases, expressions are used to limit the returned data from a SELECT statement.

The syntax to use an expression is:

   SELECT expression
   

Expression can be any valid SQL SELECT statement. Refer to Chapter 4 for more information on SELECT statements.

To illustrate, the following function inserts a timestamp into a column of an existing database table by using the now function to get the current time. The time is then stored into a variable that is inserted into a table.

Example 9-18. Using SELECT Expressions in PL/pgSQL

   CREATE FUNCTION date_ship(integer) RETURNS timestamp AS'
      DECLARE
         -- variable name for the invoice number input
         inv_num ALIAS FOR $1;

         -- variable to hold the current time
         curr_time timestamp;

      BEGIN
         -- grabs the current time
            curr_time := ''NOW'';

         -- inserts the current time and invoice number
         -- into the shipped_orders table
         INSERT INTO shipped_orders (invoice_num, ship_date)
                VALUES (inv_num, curr_time);

         -- displays the time inserted into the table
         RETURN curr_time;

      END;
   ' LANGUAGE 'plpgsql';
   

If you use the previously defined date_ship function and specify to add invoice number 202:

   SELECT date_ship(202);
   

Then it displays this:

  date_ship
------------------------
 2001-07-07 12:41:06-07
(1 row)
   

The shipped_orders table should also have a row with those values inserted:

 invoice_num | subtotal | cust_id | ship_date
-------------+----------+---------+------------
         272 |   $13.50 |      28 | 2001-02-15
         280 |   $11.75 |      21 | 2001-03-01
         291 |   $16.99 |      10 | 2001-04-20
         273 |   $14.25 |      10 | 2001-04-19
         274 |   $26.97 |      10 | 2001-05-15
         275 |   $41.35 |      28 | 2001-03-09
         201 |   $35.00 |      15 | 2001-07-06
         202 |          |         | 2001-07-07
(8 rows)
   

Notice that the newly added row does not have the time stamped into the row. That is because the column is a date data type, not timestamp . Therefore, when dealing with tables, the constraints on the table still applies. For more information on constraints, refer to the section called Constraints in Chapter 5 .

	Statement errors
	Any type of statement not understood by the PL/pgSQL parser is still put in a query and sent to the database for execution. Once executed, the result will return nothing.