SoFunction
Updated on 2025-04-07

Advanced explanation of PostgreSQL stored procedures (including cursors, error handling, custom functions, transactions)

introduce

In the previous article, we explained the basic introduction to PostgreSQL stored procedures, which meets some of the simplest uses. This chapter introduces relatively complex usage methods.

cursor

The PL/pgSQL cursor allows us to encapsulate a query and then process a record in the result set each time. A cursor can split a large result set into many small records to avoid memory overflow; in addition, we can define a function that returns a cursor reference, and the caller can process the returned result set based on this reference.

The steps to use a cursor are generally as follows:

  • Declare cursor variables;
  • Open the cursor;
  • Get results from cursors;
  • Determine whether there are more results. If present, perform step 3; otherwise, perform step 5;
  • Close the cursor.

We directly demonstrate the process of using cursors with an example:

DO $$
DECLARE 
  rec_emp RECORD;
  cur_emp CURSOR(p_deptid INTEGER) FOR
    SELECT first_name, last_name, hire_date 
    FROM employees
    WHERE department_id = p_deptid;
BEGIN
  -- Open the cursor
  OPEN cur_emp(60);

  LOOP
    -- Get records in the cursor
    FETCH cur_emp INTO rec_emp;
    -- Exit the loop when no more data is found
    EXIT WHEN NOT FOUND;

    RAISE NOTICE '%,% hired at:%' , rec_emp.first_name, rec_emp.last_name, rec_emp.hire_date;
  END LOOP;

  -- Close the cursor
  CLOSE cur_emp;
END $$;

NOTICE:  Alexander,Hunold hired at:2006-01-03
NOTICE:  Bruce,Ernst hired at:2007-05-21
NOTICE:  David,Austin hired at:2005-06-25
NOTICE:  Valli,Pataballa hired at:2006-02-05
NOTICE:  Diana,Lorentz hired at:2007-02-07

First, a cursor cur_emp is declared, and a query statement is bound to obtain employees of the specified department through a parameter p_deptid; then use OPEN to open the cursor; then use the FETCH statement in the loop to obtain the records in the cursor, and exit the loop statement if no more data is found; the variable rec_emp is used to store the records in the cursor; finally use the CLOSE statement to close the cursor and release the resources.

Cursor is a powerful data processing function in PL/pgSQL. For more usage methods, please refer toOfficial Documentation。

Error handling

Report errors and information

PL/pgSQL provides RAISE statements for printing messages or throwing errors:

RAISE level format;

Different levels represent different severity levels of error, including:

DEBUG
LOG
NOTICE
INFO
WARNING
EXCEPTION

In the example above, we often use NOTICE to output some information. If level is not specified, default to EXCEPTION, an exception will be thrown and the code will be terminated.

format is a string used to provide information content. It can use the percent sign (%) placeholder to receive the value of the parameter. Two consecutive percent signs (%%) represent the output percent sign itself.

Here are some RAISE examples:

DO $$ 
BEGIN 
  RAISE DEBUG 'This is a debug text.';
  RAISE INFO 'This is an information.';
  RAISE LOG 'This is a log.';
  RAISE WARNING 'This is a warning at %', now();
  RAISE NOTICE 'This is a notice %%';
END $$;

INFO:  This is an information.
WARNING:  This is a warning at 2020-05-16 11:27:06.138569+08
NOTICE:  This is a notice %

As can be seen from the results, not all messages are printed to the client and server logs. This can be set by the configuration parameters client_min_messages and log_min_messages.

For EXCEPTION level errors, additional options can be supported:

RAISE [ EXCEPTION ] format USING option = expression [, ... ];
RAISE [ EXCEPTION ] condition_name USING option = expression [, ... ];
RAISE [ EXCEPTION ] SQLSTATE 'sqlstate' USING option = expression [, ... ];
RAISE [ EXCEPTION ] USING option = expression [, ... ];

Where, option can be the following options:

MESSAGE, set the error message. If the format string is already included in the RAISE statement, this option cannot be used anymore.
DETAIL, specify error details.
HINT, set a prompt message.
ERRCODE, specify an error code (SQLSTATE). It can be a conditional name in the document or a SQLSTATE code composed of five characters.
COLUMN, CONSTRAINT, DATATYPE, TABLE, SCHEMA, return the name of the related object.
Here are some examples:

RAISE EXCEPTION 'Nonexistent ID --> %', user_id
      USING HINT = 'Please check your user ID';

RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';

RAISE division_by_zero;
RAISE SQLSTATE '22012';

Check assertions

PL/pgSQL provides ASSERT statements for debugging stored procedures and functions:

ASSERT condition [ , message ];

where condition is a Boolean expression; if its result is true, ASSERT passes; if the result is false or NULL, an ASSERT_FAILURE exception will be thrown. message is used to provide additional error information, default is "assertion failed". For example:

DO $$ 
DECLARE 
  i integer := 1;
BEGIN 
  ASSERT i = 0, 'i should be 0!  ';
END $$;

ERROR:  i The initial value should be 0！
CONTEXT:  PL/pgSQL function inline_code_block line 5 at ASSERT

⚠️Note that ASSERT is only suitable for code debugging; use RAISE statement to output error messages.

Catch exceptions

By default, when PL/pgSQL encounters an error, code execution will be terminated and transactions will be cancelled. We can also use EXCEPTION in the code block to catch the error and continue the transaction:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

If a code execution error occurs, the program will enter the EXCEPTION module; match the condition in turn, find the first matching branch and execute the corresponding handler_statements; if no matching branch is found, continue to throw an error.

Here is an example of a zero-dividing error:

DO $$ 
DECLARE 
  i integer := 1;
BEGIN 
  i := i / 0;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Exercise zero error!  ';
  WHEN OTHERS THEN
    RAISE NOTICE 'Other errors!  ';
END $$;

NOTICE:  Zero-deletion error！
OTHERS Used to catch unspecified error types。

PL/pgSQL also provides GET STACKED DIAGNOSTICS statements that capture detailed error information. For details, please refer toOfficial Documentation。

Custom functions

To create a custom PL/pgSQL function, use the CREATE FUNCTION statement:

CREATE [ OR REPLACE ] FUNCTION
  name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  RETURNS rettype
AS $$
DECLARE
  declarations
BEGIN
  statements;
  ...
END; $$
LANGUAGE plpgsql;

CREATE means creating a function, OR REPLACE means replacing the function definition; name is the function name; brackets are parameters, and multiple parameters are separated by commas; argmode can be IN (input), OUT (output), INOUT (input and output), or VARIADIC (variable quantity), default is IN; argname is the parameter name; argtype is the type of the parameter; default_expr is the default value of the parameter; rettype is the type of the return data; AS is followed by the function definition, the same as the anonymous block above; finally, LANGUAGE specifies the language implemented by the function, or it can be other procedural languages.

The following example creates a function get_emp_count that returns the number of employees in a specified department:

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer)
 RETURNS integer 
AS $$
DECLARE
  ln_count integer;
BEGIN
  select count(*) into ln_count
  from employees
  where department_id = p_deptid;

  return ln_count;
END; $$
LANGUAGE plpgsql;

After creating this function, you can call it in SQL statements like built-in functions:

select department_id,department_name,get_emp_count(department_id)
from departments d;
department_id|department_name     |get_emp_count|
-------------|--------------------|-------------|
           10|Administration      |            1|
           20|Marketing           |            2|
           30|Purchasing          |            6|
...

PL/pgSQL functions support overloading, that is, the same function name has different function parameters. For example, the following statement creates an overloaded function get_emp_count, which returns the number of employees who join after the specified date of the specified department:

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer, p_hiredate date)
 RETURNS integer 
AS $$
DECLARE
  ln_count integer;
BEGIN
  select count(*) into ln_count
  from employees
  where department_id = p_deptid and hire_date >= p_hiredate;

  return ln_count;
END; $$
LANGUAGE plpgsql;

Check the number of employees who joined each department after 2005:

select department_id,department_name,get_emp_count(department_id),get_emp_count(department_id, '2005-01-01')
from departments d;
department_id|department_name     |get_emp_count|get_emp_count|
-------------|--------------------|-------------|-------------|
           10|Administration      |            1|            0|
           20|Marketing           |            2|            1|
           30|Purchasing          |            6|            4|
...

Let's take a look at another example of VARIADIC parameters:

CREATE OR REPLACE FUNCTION sum_num(
  VARIADIC nums numeric[])
  RETURNS numeric
AS $$
DECLARE ln_total numeric;
BEGIN
  SELECT SUM(nums[i]) INTO ln_total
  FROM generate_subscripts(nums, 1) t(i);

  RETURN ln_total;
END; $$
LANGUAGE plpgsql;

Parameter nums is an array that can pass in as many parameters; then calculate their sum. For example:

SELECT sum_num(1,2), sum_num(1,2,3);
sum_num|sum_num|
-------|-------|
      3|      6|

If the function does not need to return the result, you can return the void type; or use stored procedures directly.

Stored procedures

PostgreSQL 11 adds stored procedures and is created using the CREATE PROCEDURE statement:

CREATE [ OR REPLACE ] PROCEDURE
  name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
AS $$
DECLARE
  declarations
BEGIN
  statements;
  ...
END; $$
LANGUAGE plpgsql;

The main difference between the definition of stored procedures and functions is that there is no return value, and other contents are similar. The following example creates a stored procedure update_emp that modifies employee information:

CREATE OR REPLACE PROCEDURE update_emp(
  p_empid in integer,
  p_salary in numeric,
  p_phone in varchar)
AS $$
BEGIN
  update employees 
  set salary = p_salary,
      phone_number = p_phone
  where employee_id = p_empid;
END; $$
LANGUAGE plpgsql;

Calling stored procedures uses CALL statements:

call update_emp(100, 25000, '515.123.4560');

Transaction Management

Within a stored procedure, transactions can be submitted or rolled back using COMMIT or ROLLBACK statements. For example:

create table test(a int);

CREATE PROCEDURE transaction_test()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END
$$;

CALL transaction_test();
select * from test;
a|
-|
0|
2|
4|
6|
8|

Only even numbers will be finalized.

Everyone is welcome to comment and like. Most of this article comes from the understanding of the official website documents and my own experience. If you like it, you will explain the content of Oracle stored procedures. Thank you for your attention!

This is the article about this advanced introduction to PostgreSQL stored procedures (including cursors, error handling, custom functions, transactions). For more related PostgreSQL stored procedures, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!