Which function body correctly implements this using RETURNS TABLE?
hard📝 Application Q8 of 15
PostgreSQL - Advanced PL/pgSQL
You want to create a PostgreSQL function that returns a table with columns user_id INT and email TEXT from a users table, but only for users with verified emails (verified = TRUE). Which function body correctly implements this using RETURNS TABLE?
ACREATE FUNCTION get_verified_users() RETURNS SETOF users AS $$ BEGIN RETURN QUERY SELECT * FROM users WHERE verified = TRUE; END; $$ LANGUAGE plpgsql;
BCREATE FUNCTION get_verified_users() RETURNS TABLE(user_id INT, email TEXT) AS $$ BEGIN RETURN SELECT id, email FROM users WHERE verified = TRUE; END; $$ LANGUAGE plpgsql;
CCREATE FUNCTION get_verified_users() RETURNS TABLE(user_id INT, email TEXT) AS $$ BEGIN RETURN QUERY SELECT id, email FROM users WHERE verified = TRUE; END; $$ LANGUAGE plpgsql;
DCREATE FUNCTION get_verified_users() RETURNS TABLE(user_id INT, email TEXT) AS $$ BEGIN RETURN NEXT SELECT id, email FROM users WHERE verified = TRUE; END; $$ LANGUAGE plpgsql;
Step-by-Step Solution
Solution:
Step 1: Use RETURNS TABLE with column definitions
Function must declare columns user_id and email.
Step 2: Use RETURN QUERY for set returning
RETURN QUERY executes the SELECT and returns all rows.
Step 3: Analyze options
CREATE FUNCTION get_verified_users() RETURNS TABLE(user_id INT, email TEXT) AS $$ BEGIN RETURN QUERY SELECT id, email FROM users WHERE verified = TRUE; END; $$ LANGUAGE plpgsql; correctly uses RETURN QUERY with WHERE clause.
Step 4: Identify incorrect options
B uses RETURN instead of RETURN QUERY, C returns SETOF users (not table), D misuses RETURN NEXT with SELECT.
Final Answer:
CREATE FUNCTION get_verified_users() RETURNS TABLE(user_id INT, email TEXT) AS $$ BEGIN RETURN QUERY SELECT id, email FROM users WHERE verified = TRUE; END; $$ LANGUAGE plpgsql; is correct.
Quick Check:
Use RETURN QUERY for returning multiple rows. [OK]
Quick Trick:Use RETURN QUERY to return multiple rows [OK]
Common Mistakes:
Using RETURN instead of RETURN QUERY
Returning SETOF whole table instead of specific columns
Misusing RETURN NEXT with SELECT statement
Master "Advanced PL/pgSQL" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
