MCQs on Functions and Stored Procedures | PostgreSQL Database

Enhance your PostgreSQL expertise by mastering functions, stored procedures, parameters, return types, triggers, and error handling. Learn essential concepts for writing, managing, and troubleshooting database functions effectively.

MCQs on PostgreSQL Functions and Stored Procedures

Section 1: Writing Functions in SQL and PL/pgSQL

1. What is the purpose of a function in PostgreSQL?
   • A) To define a database schema
   • B) To encapsulate reusable logic in the database
   • C) To execute DDL statements
   • D) To define database constraints
2. Which language is commonly used for writing functions in PostgreSQL?
   • A) Python
   • B) PL/pgSQL
   • C) JavaScript
   • D) HTML
3. What is the syntax for creating a function in PostgreSQL?
   • A) CREATE FUNCTION function_name AS $$
   • B) CREATE FUNCTION function_name RETURNS datatype AS $$
   • C) FUNCTION function_name RETURNS datatype BEGIN $$
   • D) CREATE function_name BEGIN $$
4. Which of the following is a mandatory clause in a PostgreSQL function?
   • A) RETURNS
   • B) LANGUAGE
   • C) BEGIN
   • D) Both A and B
5. In PL/pgSQL, how do you declare a variable inside a function?
   • A) var variable_name datatype;
   • B) DECLARE variable_name datatype;
   • C) VARIABLE variable_name AS datatype;
   • D) LET variable_name datatype;

Section 2: Function Parameters and Return Types

6. What are the three types of parameters in PostgreSQL functions?
   • A) IN, OUT, INOUT
   • B) REQUIRED, OPTIONAL, DEFAULT
   • C) INPUT, OUTPUT, OPTIONAL
   • D) START, END, DEFAULT
7. What is the default parameter type in PostgreSQL functions?
   • A) OUT
   • B) INOUT
   • C) IN
   • D) NONE
8. How do you define a function with multiple input parameters?
   • A) Separate them with commas in the parameter list
   • B) Use BEGIN and END for each parameter
   • C) Define each parameter in a separate CREATE FUNCTION statement
   • D) Use INOUT for all parameters
9. What is the keyword used to specify a function’s return type?
   • A) RETURNS
   • B) OUTPUT
   • C) RETURN
   • D) RESULT
10. Which of the following return types can a function in PostgreSQL use?
    • A) INTEGER
    • B) TABLE
    • C) VOID
    • D) All of the above

Section 3: Triggers and Trigger Functions

11. What is the purpose of a trigger in PostgreSQL?
    • A) To enforce database constraints
    • B) To automate actions in response to database events
    • C) To optimize query performance
    • D) To create database backups
12. Which statement is true about a trigger function in PostgreSQL?
    • A) It must be invoked manually by a user
    • B) It is automatically invoked by a specific event
    • C) It can only be created in SQL language
    • D) It cannot modify data in the database
13. How is a trigger function associated with a table?
    • A) By defining it directly within the table schema
    • B) Using the CREATE FUNCTION command
    • C) Using the CREATE TRIGGER command
    • D) By calling it in an INSERT statement
14. Which event can a trigger function NOT respond to?
    • A) INSERT
    • B) DELETE
    • C) DROP
    • D) UPDATE
15. What is the syntax for defining a trigger function in PL/pgSQL?
    • A) CREATE TRIGGER FUNCTION function_name AS $$
    • B) CREATE FUNCTION function_name RETURNS TRIGGER AS $$
    • C) FUNCTION trigger_name RETURNS VOID AS $$
    • D) CREATE TRIGGER trigger_name FOR FUNCTION function_name $$
16. How do you specify the timing of a trigger?
    • A) BEFORE or AFTER
    • B) FIRST or LAST
    • C) BEGIN or END
    • D) PRE or POST
17. Which keyword is used in a trigger function to reference the new data being inserted?
    • A) NEW
    • B) OLD
    • C) CURRENT
    • D) INSERTED
18. What is the maximum number of triggers that can be associated with a table?
    • A) 5
    • B) 10
    • C) Unlimited
    • D) One per table
19. What is the difference between a row-level and a statement-level trigger?
    • A) Row-level triggers execute once per statement; statement-level triggers execute once per row.
    • B) Row-level triggers execute once per row; statement-level triggers execute once per statement.
    • C) Both execute at the same frequency.
    • D) Only row-level triggers are supported in PostgreSQL.
20. How do you drop a trigger in PostgreSQL?
    • A) DELETE TRIGGER trigger_name ON table_name;
    • B) DROP FUNCTION trigger_name;
    • C) DROP TRIGGER trigger_name ON table_name;
    • D) REMOVE TRIGGER trigger_name;

Section 4: Error Handling in Functions

21. How can exceptions be handled in a PL/pgSQL function?
    • A) By using the EXCEPTION block
    • B) By using the TRY-CATCH block
    • C) By using the IF-ELSE block
    • D) By using the ERROR block
22. What does the RAISE statement do in a PL/pgSQL function?
    • A) Declares a variable
    • B) Generates a user-defined error
    • C) Stops the function execution
    • D) Logs the execution progress
23. What is the correct syntax for handling an exception in PL/pgSQL?
    • A) BEGIN … END … HANDLE EXCEPTION;
    • B) BEGIN … EXCEPTION … WHEN condition THEN … END;
    • C) TRY … CATCH … END TRY;
    • D) BEGIN … WHEN ERROR THEN … END;
24. How do you log messages in PL/pgSQL functions?
    • A) Using the PRINT statement
    • B) Using the LOG statement
    • C) Using the RAISE NOTICE statement
    • D) Using the ERROR statement
25. Which RAISE level is used for displaying debugging information?
    • A) NOTICE
    • B) WARNING
    • C) DEBUG
    • D) ERROR
26. What happens when an unhandled exception occurs in a PL/pgSQL function?
    • A) The function continues execution
    • B) The function terminates and raises an error
    • C) The database rolls back to the previous savepoint
    • D) The exception is ignored
27. How can a specific exception condition be caught in a PL/pgSQL function?
    • A) WHEN condition THEN …
    • B) ON condition RAISE …
    • C) IF condition THEN …
    • D) CATCH condition DO …
28. How do you retry a transaction after an exception in PL/pgSQL?
    • A) Use the RETRY block
    • B) Use a loop to re-execute the transaction logic
    • C) Use the CONTINUE block
    • D) PostgreSQL automatically retries the transaction
29. What is the purpose of the GET STACKED DIAGNOSTICS statement?
    • A) To retrieve details about the last exception raised
    • B) To debug PL/pgSQL functions
    • C) To log function execution time
    • D) To validate input parameters
30. Which statement best practices ensure proper error handling in PostgreSQL functions?
    • A) Use exception blocks for critical operations
    • B) Avoid using error handling for all operations
    • C) Always log errors for debugging purposes
    • D) Both A and C

Answer Key