The structure of PL/pgSQL is fairly simple, mainly due to the fact that each portion of code is designed to exist as a function. While it may not look immediately similar to other languages, PL/pgSQL’s structure is similar to other programming languages such as C, in which each portion of code acts (and is created) as a function, all variables must be declared before being used, and code segments accept arguments when called and return arguments at their end.
Regarding its syntax, PL/pgSQL functions are case insensitive. You can use mixed, upper-,
or lowercase for keywords and identifiers. Additionally, you will notice the use of pairs of
apostrophes (single quotes) in many places within this chapter. These are required whenever a
single apostrophe would ordinarily be used. The pair of apostrophes is a means to escape an
apostrophe within the function definition to PostgreSQL, since a function definition is
actually a large string constant within a CREATE FUNCTION
statement.
This section will discuss the block organization of PL/pgSQL code, how to use comments, how PL/pgSQL expressions are organized, and the usage of statements.
PL/pgSQL code is organized in blocks of code. This method of organization is known as
block structured code. Code blocks are entered within a SQL CREATE FUNCTION
call that creates the PL/pgSQL function in the PostgreSQL
database. This CREATE FUNCTION
command names the new function, states its
argument types, and states the return type. The function’s main code block then starts with a
declaration section.
All variables are declared and optionally initialized to a default value in the
declaration section of a code block. A variable declaration specifies the variable’s name and
type. The declaration section is denoted by the DECLARE
keyword. Each
variable declaration is ended with a semicolon.
After declaring variables, the main body of the code block is started with the BEGIN
keyword. The code block’s statements should appear after the BEGIN
keyword.
The END
keyword designates the end of the code block. The main block
of a PL/pgSQL function should return a value of its specified return type and end any
sub-blocks (code blocks started within another code block) before its END
keyword is reached.
Example 11-5 shows the structure of a PL/pgSQL code block.
Example 11-5. Structure of a PL/pgSQL code block
CREATE FUNCTION identifier (arguments) RETURNS type AS ' DECLARE declaration; [...] BEGIN statement; [...] END; ' LANGUAGE 'plpgsql';
A block of PL/pgSQL code can contain an unlimited amount of sub-blocks, which are code blocks nested within other code blocks. Sub-blocks are read and interpreted in the same manner as normal blocks; hence, they may also contain sub-blocks of their own.
Sub-blocks can be useful for the organization of code within a large PL/pgSQL function.
All sub-blocks must follow normal block structure, meaning they must start with the DECLARE
keyword, followed by the BEGIN
keyword and a body of
statements, then end with the END
keyword.
There are two methods of commenting in PL/pgSQL, both similar to the comment structure of other programming languages. The two methods are single-line comments, and block comments (multiple line comments).
The first method of commenting is single line commenting. Single line comments begin with two dashes (- -) and have no end-character. The parser interprets all characters on the same line after the two dashes as part of the comment. Example 11-6 demonstrates the use of single line comments.
The second type of comment is the multiline or block comment, which
should be familiar to most anyone who has worked with programming languages before. Block
comments begin with the forward slash and asterisk characters (/*
) and end
with the asterisk and forward slash characters (*/
). Block comments can
span multiple lines, and any text between the opening /*
and closing
*/
is considered a comment. Example 11-7 shows
the correct usage of a block comment.
In any programming language, it is helpful to write useful comments. A comment is considered useful if it can express to the user why a certain section of code was designed a certain way, or why syntax was used in an abnormal or creative manner. Comments that restate what is happening programmatically can be helpful at times, but you must remain aware of what is happening in your program and be sure to express why certain things are being done (instead of just how).
In our PL/pgSQL code examples we will use comments to explain how and why we do certain things within a particular section of code. This is to help you, as a new PL/pgSQL user, learn more about the language and its uses.
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 ' DECLARE an_integer int4; BEGIN an_integer := 10 * 10; return an_integer; END; ' LANGUAGE 'plpgsql';
Example 11-9. Output of a_ function( )
booktown=# SELECT a_function( ) AS output; output -------- 100 (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 - - 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; BEGIN - - 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; END; ' LANGUAGE 'plpgsql';
18.225.235.144