How to Create a PostgreSQL Function to Return a Table
When you have a PostgreSQL database and you want to check records from the table, using a function periodically is the most convenient way, especially a PostgreSQL function that returns a table in its result set. This way, you encapsulate your result set, and using this approach helps with a better code organization.
The following is the syntax to create a PostgreSQL function that returns a table:
RETURNS TABLE(column_list)
AS $$
BEGIN RETURN QUERY(query);
END;
$$ LANGUAGE plpgsql
The good thing about creating such functions is that it allows you to specify various “column_list” instead of returning a single value from your table. Let’s have two examples to help us understand what steps to follow.
Example 1: Working with a Single Input
When creating a function that returns a table, you must supply the argument to use with the return query. The argument can be a pattern or a specific input. This example gives a case where we use a single input as the argument.
The following is the “student” table that we will use for our query:
In the following image, we create a function named “get_student” that takes an INT as the argument. In the RETURNS TABLE section, we return a table with four columns: the “student_id”, “student_name”, “student_faculty”, and “current_status”.
All these columns get their values from the return query that we define. Notice that the return query uses a WHERE statement using the parameter list that we specify when creating the function.
Once you create the function, you will get a similar output to the one that we previously had which confirms that your PostgreSQL function was created successfully. To verify this further, run the following command to list the available functions:
We add the asterisks to match any function that has the specified name. The output shows that we have our PostgreSQL function in our database.
The last step is to test the created function. Run the “select” statement to call the function. Then, add the expected argument. For our case, the parameter is of type INT. Thus, we add 1 as our argument to retrieve the records that match it and return a table as demonstrated in the following:
Example 2: Working with an Input Pattern
When unsure of the value to use with the return query, you can use the ILIKE operator to match a given pattern. For instance, if you have a name and only know a section of the string, the ILIKE operator lets you use the “%” symbol to define how your pattern will be.
We use the following table for this case and target the name column:
We create a function that is similar to the one that we did earlier. However, the parameter type has changed and the return query uses the ILIKE operator which is added as an argument when calling the function.
Once the function is ready, we can call it to return the table. There are different ways to go about it. For instance, if the search pattern contains “Jo” in the string, we execute the command query as follows:
We match all values with “Jo” in their string, giving us two records.
If we only know the last part of a string, we twist the query and run it as follows:
Lastly, if we know the first part of the string, we add the “&” symbol after the pattern as shown in the following:
Those are the different examples on how to use the PostgreSQL function to return a table.
Conclusion
PostgreSQL is a powerful database with numerous features. When creating functions, you can set them to return a table as the result set for different reasons including achieving encapsulation. This post presented two examples of creating and using a function that returns a table in PostgreSQL.
