Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (PL / pgSQL)

Sample Question


We want to use PL/pgSQL to define a function like this:

create function test_func(a integer) returns integer as $$
x record;
select * into x from test_table
where id = a;
return x.value;
$$ language plpgsql;

Choose an appropriate explanation for this.

  1.  The create function fails because a semicolon is required after declare on the second line.
  2. The create function fails because the x record on line 3 is incorrect. The correct value is x test_table%ROWTYPE.
  3. The create function fails because a semicolon is required after begin on line 4.
  4. If the test_table referenced on line 5 does not exist, create function fails.
  5. If the id column referenced on line 6 does not exist, the create function succeeds, but a run-time error occurs when the function is called.

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

Answer and Explanation

PostgreSQL comes standard with a procedural language called PL/pgSQL. This can be used to perform complex tasks on the server side.
Functions written in PL/pgSQL are divided into a declaration starting with DECLARE, an execution starting with BEGIN, and an end with END.
The variable definition in declaration part (x record on line 3 in the example), the execution statement (SELECT statement starting on line 5 and RETURN statement on line 7 in the example), and the final END require a semicolon to mark the end of the statement. However, DECLARE and BEGIN must not be followed by a semicolon, or a syntax error will occur.
In addition to the familiar data types for database tables, such as integer and varchar, variable declarations can include row types. These represent the same structure as the specified table (written as table_name% ROWTYPE). They can also include record types to which any row structure can be assigned (line 3 in the example).
At the time of CREATE FUNCTION, only parsing is done, and the existence or types of tables and columns are not checked. For example, if the executor uses a variable that is not defined in declaration, or if the SQL syntax is incorrect, CREATE FUNCTION itself will return an error. However, even if the specified table or column does not exist, CREATE FUNCTION will succeed if the SQL syntax is correct. An error will be returned when attempting to execute the defined function.

Therefore, the correct answer is E.