How to Create Procedure in PostgreSQL: Syntax and Example

In PostgreSQL, you create a procedure using the CREATE PROCEDURE statement followed by the procedure name, parameters, and body inside LANGUAGE plpgsql. Procedures are called with CALL procedure_name() and can perform operations without returning a value.

Syntax

The basic syntax to create a procedure in PostgreSQL includes the CREATE PROCEDURE keyword, the procedure name, optional parameters, the LANGUAGE specification, and the procedure body enclosed in BEGIN ... END.

• CREATE PROCEDURE: starts the procedure definition.
• procedure_name: the name you give your procedure.
• (parameters): optional input parameters with types.
• LANGUAGE plpgsql: specifies the procedural language used.
• AS $$ ... $$: the body of the procedure inside dollar quotes.
• BEGIN ... END: the block where you write SQL commands.

sql

CREATE PROCEDURE procedure_name(parameter_name parameter_type)
LANGUAGE plpgsql
AS $$
BEGIN
    -- SQL statements here;
END;
$$;

Example

This example creates a procedure named add_numbers that takes two integer inputs and inserts their sum into a table called results. It demonstrates how to define parameters, use variables, and execute SQL inside the procedure.

sql

CREATE TABLE IF NOT EXISTS results (sum_result integer);

CREATE OR REPLACE PROCEDURE add_numbers(a integer, b integer)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO results(sum_result) VALUES (a + b);
END;
$$;

CALL add_numbers(5, 7);

SELECT * FROM results;

Output

 sum_result 
------------
         12
(1 row)

Common Pitfalls

Common mistakes when creating procedures in PostgreSQL include:

• Forgetting to specify LANGUAGE plpgsql, which causes syntax errors.
• Using RETURN statements inside procedures, which are not allowed (use functions for returning values).
• Not calling the procedure with CALL but trying to use SELECT.
• Missing semicolons inside the procedure body.

sql

/* Wrong: Missing LANGUAGE clause */
CREATE PROCEDURE wrong_proc()
AS $$
BEGIN
    RAISE NOTICE 'Hello';
END;
$$;

/* Right: Include LANGUAGE plpgsql */
CREATE PROCEDURE right_proc()
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Hello';
END;
$$;

Quick Reference

Clause	Description
CREATE PROCEDURE	Starts procedure definition
procedure_name(params)	Name and input parameters
LANGUAGE plpgsql	Specifies procedural language
AS $$ ... $$	Defines procedure body
BEGIN ... END	Block for SQL statements
CALL procedure_name()	Executes the procedure

Key Takeaways

Use CREATE PROCEDURE with LANGUAGE plpgsql to define procedures in PostgreSQL.

Procedures do not return values; use CALL to execute them.

Always include semicolons and proper syntax inside the procedure body.

Do not use RETURN statements inside procedures; use functions if you need to return data.

Remember to call procedures with CALL, not SELECT.