Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (Procedure definition)

Sample Question


Choose two appropriate explanations for the differences between the procedures (PROCEDURE) added in PostgreSQL version 11 and the functions (FUNCTION) supported from previous versions.

  1. A PROCEDURE is created with the CREATE PROCEDURE command, and a FUNCTION is created with the CREATE FUNCTION command.
  2. While PROCEDURE is defined in the SQL standard, FUNCTION is a PostgreSQL-specific extension.
  3. FUNCTION of type void is being deprecated and it is recommended to replace it with PROCEDURE.
  4. A FUNCTION is executed as SELECT func_name(arg1, arg2 ...), whereas a PROCEDURE is executed as CALL proc_name(arg1, arg2 ...).
  5. While a PROCEDURE can update data in the database, a FUNCTION can only perform calculations based on the data and cannot update the data.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

Many database systems have the capability to bundle multiple SQL operations into a single program that runs on the database server. This is commonly referred to as stored procedures, which means 'procedures stored on the server'.
Stored procedures are divided into stored functions that return values and stored procedures in the narrower sense, which are like subroutines that do not return values. Both are defined in the SQL standard. Functions are created using the CREATE FUNCTION command and are called from SQL statements such as SELECT, INSERT, and UPDATE as SELECT func_name(arg1, arg2...). They can be specified not only as columns to be selected but also as values in the WHERE clause and VALUES clause. Procedures are created using the CREATE PROCEDURE command and are called as CALL proc_name(arg1, arg2...).
Until PostgreSQL version 10, only functions were supported. There may be times when you don't need to return a value and just want to execute a series of operations. In such cases, you would create a function of type void and execute it as SELECT void_func_name(arg1, arg2...). With the support for procedures in version 11, you can now create procedures and execute them as CALL proc_name(args...). However, this does not mean that functions of type void are deprecated.
While many people might intuitively think of a function as examining the state of data and returning a value calculated from it, there are no special restrictions on what can be executed as a stored function, and it is possible to update data.

Therefore, the correct answers are A and D.