Test Your PostgreSQL DB Function Knowledge with this Quiz

Progress

True / False

A function in PostgreSQL can return a single value.

True False

Functions in PostgreSQL can return a single value, which can be of any data type, such as integer, text, or boolean.

True / False

PostgreSQL functions are created using the CREATE FUNCTION command.

True False

The CREATE FUNCTION command is used to define a new function in PostgreSQL.

True / False

Functions in PostgreSQL cannot accept input parameters.

True False

Functions in PostgreSQL can accept input parameters to perform operations based on the given input.

True / False

In PostgreSQL, functions can return a table as a result.

True False

PostgreSQL functions can return a set of rows (a table) using the SETOF keyword or by specifying a composite type.

True / False

Functions in PostgreSQL can be written in multiple languages, such as SQL, PL/pgSQL, and PL/Python.

True False

PostgreSQL supports functions written in various languages, including SQL, PL/pgSQL, PL/Python, PL/Perl, and more.

True / False

PostgreSQL functions can include transaction control commands (COMMIT, ROLLBACK).

True False

Functions in PostgreSQL cannot include transaction control commands like COMMIT or ROLLBACK. These commands are not allowed within the body of a function.

True / False

PostgreSQL supports the creation of immutable functions that always produce the same result given the same input.

True False

PostgreSQL allows the creation of immutable functions, which are functions that always return the same result for the same input and do not modify the database.

True / False

You can create a function in PostgreSQL that performs dynamic SQL execution using the EXECUTE statement.

True False

PostgreSQL functions, particularly those written in PL/pgSQL, can perform dynamic SQL execution using the EXECUTE statement.

True / False

PostgreSQL functions can use the ORACLE-specific DECODE function for conditional logic.

True False

PostgreSQL does not support the ORACLE-specific DECODE function. Instead, it uses CASE expressions for conditional logic.

Q10

True / False

When migrating functions from ORACLE to PostgreSQL, all PL/SQL functions can be directly transferred without any modification.

True False

Migrating functions from ORACLE to PostgreSQL often requires modifications because PL/SQL (used in ORACLE) and PL/pgSQL (used in PostgreSQL) have different syntax and features. Functions need to be rewritten to ensure compatibility.

Q11

Single Choice

What is the purpose of the NOW() function in PostgreSQL?

Returns the current date Returns the current timestamp Returns the current userReturns the server version

The NOW() function in PostgreSQL returns the current date and time (timestamp) with time zone.

Q12

Single Choice

Which PostgreSQL function is used to convert a string to uppercase?

LOWER() UPPER() INITCAP()LENGTH()

The UPPER() function converts all characters in a string to uppercase.

Q13

Single Choice

How do you calculate the length of a string in PostgreSQL?

LENGTH(string) LEN(string) SIZE(string)COUNT(string)

The LENGTH() function returns the number of characters in a string in PostgreSQL.

Q14

Single Choice

What does the PostgreSQL TO_CHAR() function do?

Converts a number to a character Converts a date or timestamp to a string Converts a string to a dateConverts a timestamp to an integer

The TO_CHAR() function is used to format a timestamp, date, or number into a string in a specified format in PostgreSQL.

Q15

Single Choice

Which PostgreSQL function is used to extract the day from a date or timestamp?

DATE_PART('day', timestamp) EXTRACT('day', timestamp) DAY(timestamp)TO_CHAR(timestamp, 'DD')

The EXTRACT() function is used to retrieve a subfield such as day, month, or year from a date or timestamp in PostgreSQL.

Q16

Single Choice

In PostgreSQL, how can you generate a random number between 0 and 1?

RANDOM() RAND() DBMS_RANDOM()RANDOM_NUM()

The RANDOM() function in PostgreSQL generates a random number between 0 and 1.

Q17

Single Choice

What is the output of the following PostgreSQL query?

SQL Code

SELECT ROUND(123.456, 2);

123.45 123.46 123.50123.44

The ROUND() function rounds a number to a specified number of decimal places. Here, 123.456 is rounded to 123.46 when specifying 2 decimal places.

Q18

Single Choice

Which PostgreSQL function would you use to convert a timestamp to a different time zone?

SET TIME ZONE AT TIME ZONE TO_TIMEZONE()CONVERT_TZ()

The AT TIME ZONE clause is used in PostgreSQL to convert a timestamp to another time zone.

Q19

Single Choice

How can you concatenate two strings in PostgreSQL?

CONCAT(str1, str2) str1 || str2 str1 + str2JOIN(str1, str2)

In PostgreSQL, strings can be concatenated using the || operator.

Q20

Single Choice

Given the following PostgreSQL query, what will be the result? sql

SQL Code

SELECT COALESCE(NULL, 'PostgreSQL', 'SQL');

NULL PostgreSQL SQLError

The COALESCE() function returns the first non-null value in the list. In this case, 'PostgreSQL' is the first non-null value, so it will be returned.

Q21

Multiple Choice

What is a DB function in PostgreSQL?

SQL Code

CREATE OR REPLACE FUNCTION calculate_tax(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
 RETURN price * 0.10;
END;
$$ LANGUAGE plpgsql;

A reusable set of SQL statements A procedure that returns multiple values A command used for data insertionA type of database trigger

A DB function in PostgreSQL is a reusable set of SQL statements that can return a single value or a set of values. Functions can be used to encapsulate logic, perform calculations, or manipulate data.

Q22

Multiple Choice

How can you execute a function in PostgreSQL?

SQL Code

SELECT get_customer_balance(101);

Using the SELECT statement Using the EXECUTE statement Using the CALL statementUsing the RUN statement

Functions in PostgreSQL can be executed using the SELECT statement followed by the function name and any required parameters.

Q23

Multiple Choice

What is the difference between a function and a stored procedure in PostgreSQL?

SQL Code

CREATE OR REPLACE FUNCTION get_order_total(order_id INT)
RETURNS NUMERIC AS $$
DECLARE
 total NUMERIC;
BEGIN
 SELECT SUM(amount) INTO total
 FROM order_items
 WHERE order_id = order_id;
 RETURN total;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE display_order_total(order_id INT)
LANGUAGE plpgsql AS $$
DECLARE
 total NUMERIC;
BEGIN
 total := get_order_total(order_id);
 RAISE NOTICE 'Order Total: %', total;
END;
$$;

Functions return values; procedures perform actions Procedures can manage transactions; functions cannot Functions can be used in SQL expressions; procedures cannotProcedures return multiple values; functions return a single value

The main difference between a function and a stored procedure in PostgreSQL is that functions return a value and can be used in SQL expressions, while stored procedures perform actions and do not necessarily return a value. Functions are executed with SELECT, whereas procedures use the CALL statement.

Q24

Multiple Choice

Can functions in PostgreSQL have multiple input parameters?

SQL Code

Write a SQL function named 'calculate_discounted_price' that takes the original price and a discount percentage as input and returns the discounted price. Include the SQL code to create the function.

```sql
CREATE OR REPLACE FUNCTION calculate_discounted_price(original_price NUMERIC, discount_percentage NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
 RETURN original_price - (original_price * discount_percentage / 100);
END;
$$ LANGUAGE plpgsql;
```

Yes, functions can have multiple input parameters No, functions can only have a single input parameter Yes, but only if they return a single valueYes, but they cannot return values

Yes, functions in PostgreSQL can accept multiple input parameters, allowing them to perform more complex calculations or logic based on multiple inputs.

Q25

Multiple Choice

How can you handle NULL values in a PostgreSQL function?

SQL Code

CREATE OR REPLACE FUNCTION safe_divide(numerator NUMERIC, denominator NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
 IF denominator IS NULL OR denominator = 0 THEN
 RETURN NULL;
 ELSE
 RETURN numerator / denominator;
 END IF;
END;
$$ LANGUAGE plpgsql;

Using IF statements to check for NULL Using the COALESCE function to replace NULL Using NULLIF to handle division by zeroIgnoring NULL values

In PostgreSQL, NULL values can be handled in functions using conditional logic, such as IF statements, to check for NULL and return appropriate results.

Q26

Multiple Choice

What are the performance considerations when using functions in PostgreSQL?

SQL Code

CREATE OR REPLACE FUNCTION bulk_calculate_tax(prices NUMERIC[])
RETURNS NUMERIC[] AS $$
DECLARE
 taxes NUMERIC[] := '{}';
BEGIN
 FOREACH price IN ARRAY prices LOOP
 taxes := array_append(taxes, price * 0.10);
 END LOOP;
 RETURN taxes;
END;
$$ LANGUAGE plpgsql;

Minimizing context switches between SQL and PL/pgSQL Avoiding unnecessary loops Using bulk operationsReducing the number of SQL statements within the function

Performance considerations for functions in PostgreSQL include minimizing context switches between SQL and PL/pgSQL, avoiding unnecessary loops, using bulk operations, and ensuring that the function logic is optimized for the underlying data structures.

Q27

Multiple Choice

Can functions in PostgreSQL return composite types?

SQL Code

CREATE TYPE product_details AS (
 name VARCHAR,
 price NUMERIC,
 stock INT
);

CREATE OR REPLACE FUNCTION get_product_details(product_id INT)
RETURNS product_details AS $$
DECLARE
 details product_details;
BEGIN
 SELECT name, price, stock INTO details
 FROM products
 WHERE id = product_id;
 RETURN details;
END;
$$ LANGUAGE plpgsql;

Yes, by defining and returning composite types No, functions can only return scalar values Yes, but only with specific data typesYes, using arrays instead of composite types

Yes, functions in PostgreSQL can return composite types, which allow for returning multiple related values as a single structured type.

Q28

Multiple Choice

How can you return multiple rows from a PostgreSQL function?

SQL Code

CREATE OR REPLACE FUNCTION get_top_customers()
RETURNS TABLE(customer_id INT, total_amount NUMERIC) AS $$
BEGIN
 RETURN QUERY
 SELECT customer_id, SUM(amount) as total_amount
 FROM orders
 GROUP BY customer_id
 ORDER BY total_amount DESC
 LIMIT 5;
END;
$$ LANGUAGE plpgsql;

Using RETURNS TABLE Using SETOF Using multiple RETURN statementsUsing an array to hold the rows

In PostgreSQL, you can return multiple rows from a function by using the RETURNS TABLE clause or the SETOF keyword, which allows the function to return a set of rows as a result.

Q29

Multiple Choice

Can functions in PostgreSQL execute dynamic SQL?

SQL Code

CREATE OR REPLACE FUNCTION dynamic_query_executor(table_name TEXT, condition TEXT)
RETURNS INT AS $$
DECLARE
 query TEXT;
 result INT;
BEGIN
 query := format('SELECT COUNT(*) FROM %I WHERE %s', table_name, condition);
 EXECUTE query INTO result;
 RETURN result;
END;
$$ LANGUAGE plpgsql;

Yes, using the EXECUTE command No, functions can only execute static SQL Yes, but with limited functionalityNo, dynamic SQL is only for procedures

Yes, functions in PostgreSQL can execute dynamic SQL using the EXECUTE command, which allows the function to construct and execute SQL statements at runtime.

Q30

Multiple Choice

What is the use of the RETURNS VOID clause in a PostgreSQL function?

SQL Code

CREATE OR REPLACE FUNCTION log_activity(user_id INT, activity_description TEXT)
RETURNS VOID AS $$
BEGIN
 INSERT INTO activity_log(user_id, activity_description)
 VALUES (user_id, activity_description);
END;
$$ LANGUAGE plpgsql;

It indicates that the function does not return a value It is used for functions that return a single value It allows the function to return multiple valuesIt is a placeholder for future return types

The RETURNS VOID clause in a PostgreSQL function indicates that the function does not return a value. It is used for functions that perform actions, such as inserts, updates, or deletions, without returning any data.

PostgreSQL Database Quiz Questions