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;
 
Search WWH ::




Custom Search