PostgreSQL: Function vs Stored Procedure in SQL

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Introduction

What’s the difference between a Function vs Stored Procedure in SQL? It’s a question that we hear quite often and we hope we can you provide you with the answer in this

Function vs Stored Procedure

Functions and procedural statements are sets of sql statements which can be called to execute in a given process which would involve running similar statements repetitively. The procedural statements may include a set of flow-of-controls, declarations, loops, and assignments, which can be invoked during a specific program execution using an sql interface. This help in eliminating the grind of duplicating code to complete a given program task.

The main difference between function and stored procedure is that user-defined functions do not execute transactions. This means, inside a given function you cannot open a new transaction, neither can you commit or rollback the current transaction. It is important to note that stored procedures are just functions that can support transactions and were introduced in Postgresql 11. PostgreSQL functions can be created in a variety of languages including SQL, PL/pgSQL, C, Python.

This articles illustrates the application of functions using PostgreSQL.

The syntax for creating a PostgreSQL function is provided below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
  DECLARE

declaration;
  [...]

  BEGIN

  < function_body >
  [...]
  RETURN { variable_name | VALUE }

  END; LANGUAGE plpgsql;

Below is a description of the terms used in the syntax above:

function-name indicates the name of the function.
[OR REPLACE] in an option to modify an existing function if the current function has the same name as the original function.
Note : Any complete function must have a return statement.
RETURN statement tells the function the type of data it is going to return. The return_datatype in this case are normally a base, domain type or a composite. It can also be a reference to the type of a table column.
function-body has the set of executable statements.
The AS keyword is applied when creating a standalone function.
plpgsql represents the name of the language in which the function is implemented. In our examples here we use PostgreSQL, as mentioned earlier , the language can also be SQL, C, internal, or any other user-defined procedural language.

In our application example we will use a student’s table and manipulate it information using a user-defined function. We first use a select statement to view the table content.

1
2
3
4
5
6
7
8

Now we will create a function called totalMarks() which will return the total marks of the four students above. We will then call the function to see the results. To sum the marks of the students we utilize the inbuilt aggregate function SUM().

1
2
3
4
5
6
7
8
9

articles=# CREATE OR REPLACE FUNCTION totalMarks ()
articles-# RETURNS DOUBLE PRECISION AS $total$
articles$# DECLARE
articles$# total DOUBLE PRECISION;
articles$# BEGIN
articles$# SELECT SUM(marks) AS total FROM student;
articles$# RETURN total;
articles$# END;
articles$# $total$ LANGUAGE plpgsql;

The result of this statement is CREATE FUNCTION.

The following syntax is use when calling stored function:

1	SELECT function_name();

In our case, we are calling totalMarks();

1	SELECT totalMarks();

Results

1
2
3
4
5
6
7

articles=# SELECT totalMarks ();
total
---------------

182

(1 ROW)

Stored procedures

As mentioned earlier, Stored procedures are user defined functions that can execute transactions. We use the following syntax to create procedures.

1
2
3
4
5

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE language_name
AS $
stored_procedure_body;
$;

Below is a description of the terms used in writing procedures.

CREATE PROCEDURE clause precedes the name of the stored procedure procedure_name.
Parameter_list refers to the list of parameters and is similar to user-defined functions.
PLpgSQL is the programing language used to write the procedure. It can be done in other languages such as the SQL.
The procedure statements are placed after the AS key word.
The double dollar ($) sign indicate the end of the procedure.

Example of stored procedure for correcting students marks

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE PROCEDURE correctMarks(INT, INT, DEC)
LANGUAGE plpgsql
AS $
BEGIN
    -- subtracting the amount from the student's record
    UPDATE students
    SET marks = marks- $10
    WHERE id = $1;

    -- adding the marks to the other student's record
    UPDATE students
    SET marks = marks + $10
    WHERE id = $2;

    COMMIT;
END;
$;

Calling the stored procedure require use of CALL statements

Call syntax:

1	CALL stored_procedure_name(parameter_list);

In our case, the call statement will be as follows

1	CALL correctMarks(1, 2, 100);

Conclusion

We hope you now understand the difference between a function vs stored procedure in SQL and you can apply it in your specific application. Thank you for keeping up with us at Object Rocket and if you need any help managing your data please don’t hesitate to ask us questions about your specific project.

Friday, September 18, 2020