Database Reference
In-Depth Information
count integer := 0;
table_records RECORD;
BEGIN
FOR table_records IN EXECUTE query
LOOP
count := count + 1;
END LOOP;
RETURN count;
END;
$$ LANGUAGE 'plpgsql';
When we ran a query against this function on the
warehouse_tbl
table, it counts the
rows brought by the
SELECT
query.
warehouse_db=# SELECT for_loop_query('SELECT * FROM
record.warehouse_tbl');
for_loop_query
----------------
3
(1 row)
Exception handling
To err is coder!
People make mistakes everywhere, and programming is no exception to it. PL/
pgSQL has so far shown that it contains strengths of a programming language, and it
did not leave its users with a void when it comes to error handling.
Implementing
RAISE
statements takes care of error handling in an eficient manner
with the options of
NOTICE
,
DEBUG
, and
EXCEPTION
. By deining the level of issues
it should raise, it sends information to be displayed and logs it in PostgreSQL logs,
which are usually in
/var/log/messages
.
The
RAISE
statement is a statement and allowed in the
statements
section of a PL/
pgSQL block.
The syntax for a
RAISE
statement is as follows:
RAISE NOTICE|DEBUG|EXCEPTION ''your message string'';
The following is an example of a simple
RAISE
statement:
IF ( a is not an int ) then
Return a;
Else
Raise NOTICE 'a is integer, enter string';
