Database Reference
In-Depth Information
Inside the
declaration
section of the block, variables are declared, sometimes with
an optional default value. This section is identiied by the
DECLARE
keyword. The
declaration section contains a variable name and its data type ends with a semicolon.
Any variable, row, or records used in a block or its sub-block should be declared
here, with an exception to the
FOR
loop. This can be represented as follows:
DECLARE
declaration;
You have deined the function name with its return type and declared variables
as well, so let's now start looking at the main body of the function with the
BEGIN
keyword. Its syntax is as follows:
BEGIN
Statement;
The
Statement
section can further contain unlimited sub-blocks. The nested code
blocks are read and interpreted as normal blocks and should be written using same
methods done for normal PL/pgSQL blocks. In other words, start with the
DECLARE
keyword, followed by the
BEGIN
keyword, then the body of statements, and inally
end with the
END
keyword.
Remember that
BEGIN
/
END
does not start or stop any
transaction but used for grouping of statements.
The
END
keyword ends the code block as shown in the following statement:
END;
LANGUAGE 'plpgsql';
The main body of a PL/pgSQL function should return a value of the type
deined earlier and end any sub-block before the low reaches the
END
keyword.
Let's start writing a function in PL/pgSQL.
The
getRecords()
function in the following example will simply return the total
number of records present in the
warehouse_tbl
table of the
warehouse_db
database. This is the same database we created in the previous chapter:
CREATE OR REPLACE FUNCTION getRecords()
RETURNS INTEGER AS $$
DECLARE
total INTEGER;
BEGIN
SELECT COUNT(*) INTO total FROM warehouse_tbl;