Sample Exam Questions

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

Sample Question


CREATE FUNCTION func_add(x integer, y integer) RETURNS integer AS $$
SELECT x + y;


Choose three appropriate explanations for the above.

  1. PL/pgSQL is used to define a function.
  2. It defines a function called func_add.
  3. SELECT func_add(2, 3) returns 5.
  4. ' (single quote) may replace $$ at the end of line 1 and the beginning of line 3.
  5. CREATE FUNCTION requires administrator privileges on the database.

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

Answer and Explanation

In PostgreSQL, users can define functions not only in SQL and PL/pgSQL, which adds procedural language functionality to SQL, but also in procedural languages such as C and Python.
In the example, a function named func_add of type integer is defined using CREATE FUNCTION.
The body of the function is enclosed within two $$ and consists only of the part "SELECT x + y;". The function is defined using SQL, as indicated by "LANGUAGE SQL" at the end of the definition. If PL/pgSQL is used, it would end with "LANGUAGE plpgsql".
Arguments passed to the function can be referenced either by name, as in the case of x and y in the example, or by number, as in the case of defining arguments by type only, like "CREATE FUNCTION func_x(integer, integer)" and referencing them as $1, $2.
A function defined in SQL returns the result of the last executed SQL as the function's result. For instance, func_add(2, 3) would return the result of "SELECT 2 + 3", which is 5.

In PostgreSQL function definitions, the body of the function is specified as a string. While the SQL standard encloses strings in single quotes, PostgreSQL allows the use of $$ as a substitute for single quotes, as shown in the example. When using single quotes within a string, it can be confusing to have to double them up, as in 'It isn''t easy', but with $$, you can write it as $$It isn't easy$$, which is particularly convenient for function definitions. In the example, since there are no characters in the body of the function definition that require special attention, such as single quotes, it is possible to replace the two $$ with single quotes.
Defining functions with CREATE FUNCTION does not require any special privileges and can be executed by any user.

Therefore, the correct answers are B, C, and D.