You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
Every piece of PL/pgSQL code is organised into blocks. Understanding the block structure is essential before writing functions or triggers.
[ DECLARE
declarations ]
BEGIN
statements
[ EXCEPTION
WHEN condition THEN
handler_statements ]
END;
| Section | Required? | Purpose |
|---|---|---|
| DECLARE | No | Declare local variables and cursors |
| BEGIN…END | Yes | The executable statements |
| EXCEPTION | No | Handle runtime errors |
The DO statement executes an anonymous (unnamed) block — perfect for one-off scripts:
DO $$
BEGIN
RAISE NOTICE 'Hello, PL/pgSQL!';
END;
$$;
RAISE NOTICE prints a message to the client. It is the PL/pgSQL equivalent of DBMS_OUTPUT.PUT_LINE in Oracle.
DO $$
DECLARE
v_message TEXT := 'Hello!';
v_count INTEGER := 0;
BEGIN
v_count := v_count + 1;
RAISE NOTICE 'Message: %, Count: %', v_message, v_count;
END;
$$;
The % in RAISE NOTICE is a placeholder substituted by the following arguments (like printf in C).
RAISE DEBUG 'debug info';
RAISE INFO 'informational';
RAISE NOTICE 'notice to client'; -- most commonly used
RAISE WARNING 'warning message';
RAISE EXCEPTION 'this is an error'; -- raises an exception, aborts the block
Each level maps to a PostgreSQL message severity. RAISE EXCEPTION actually throws a runtime error.
Blocks can be nested. The inner block can read outer variables but the outer block cannot read inner variables:
DO $$
DECLARE
v_outer TEXT := 'outer';
BEGIN
DECLARE
v_inner TEXT := 'inner';
BEGIN
RAISE NOTICE '% and %', v_outer, v_inner;
END;
-- v_inner is not in scope here
RAISE NOTICE '%', v_outer;
END;
$$;
-- Single-line comment
/*
Multi-line
comment
*/
PL/pgSQL uses := or = for assignment (both are accepted):
v_count := 10;
v_name = 'Alice'; -- also valid
Use || or the concat() function:
RAISE NOTICE 'Hello, %!', first_name || ' ' || last_name;
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.