As an alternative to the RETURN QUERY syntax, Postgres allows you to process rows individually and append them to the final table that is returned by your function. This approach requires having a loop defined along with the RETURN NEXT command.
The example below demonstrates how to use the RETURNS TABLE with RETURN NEXT command:
CREATE FUNCTION get_upcoming_events_and_attendees()
RETURNS TABLE (
event_name varchar,
count_of_registered_attendees int
) AS $$
DECLARE
curr_rec record;
BEGIN
FOR curr_rec IN (SELECT id, name FROM event WHERE event_date > current_date) LOOP
event_name := curr_rec.name;
count_of_registered_attendees := (SELECT COUNT(1) FROM event_attendee WHERE event_id = curr_rec.id);
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;
Since we want to process each record individually, before we append a given record to the final table, we create a FOR LOOP statement to loop through the upcoming events. The variable curr_rec of type record is used to loop through rows in the query result. You can get individual fields in a row with the dot notation: curr_rec.name is the name of the event in the current row. For each event, the number of registered attendees is retrieved.
The final table is built in each iteration of the FOR LOOP as the RETURN NEXT command adds the current row to the final result set. The variables event_name and count_of_registered_attendees, (i.e., the columns of the returned table) are used to create new rows in the function result. The RETURN NEXT command adds a new row with the current values of event_name and count_of_registered_attendees.
Note: RETURN NEXT and RETURN QUERY store the entire result set before returning from the function. That means performance might be poor for very large result sets.
Create a new function named get_topics() that has no arguments and returns a table with the following columns:
topic_id inttopic_name varcharcreated_year int (the year when a given topic was created)In the body of this function, define a FOR LOOP that will loop through the existing topics from the topic table. Use the RETURN NEXT command to build up the final table.
The topic_name should be lowercase, and all spaces in the topic name have to be replaced by an underscore.
You might find the following built-in functions useful:
EXTRACT()REPLACE()LOWER()And here's how to use them in a FOR loop:
topic_id := curr_rec.id; topic_name := REPLACE(LOWER(curr_rec.name), ' ', '_'); created_year := EXTRACT(year FROM curr_rec.created_date);
