One thing I started doing last week was writing PL/pgSQL functions for each of my JasperReports. Each function essentially just runs and returns the rows of a query. One question that I had for a long time was how can I return these results from a function without looping through the results within the function. I finally found the answer and can show you how with the example function below:

Results

If we call the above function using SELECT * FROM get_recent_days() we will get the following table output:

How Does It Work?

The first thing you will notice is that we are returning a TABLE according to the definition:

... RETURNS TABLE ("date_type" VARCHAR, "date_value" DATE, "date_sql" VARCHAR) ...

By specifying that we are returning a TABLE and then by listing the column definition list we are clearly indicating what the table will always look like when it comes out of this function.

Another thing you will notice is that we are using RETURN QUERY SELECT ... to return our query results.

Final Comments

There are a few benefits to writing a function that has to return a query result this way:

  • No need to create a type and then return that type. Placing the column definition list in the function definition keeps both the return type in plain sight when looking at the function.
  • Using RETURN QUERY makes it so that we don’t have to loop through and return each row separately.
  • Specifying the column definition list in the function definition makes it so that we avoid having to place the column definition list in the SELECT statement when calling the function.

I hope that this simple code example helps you understand an easy way to define a PL/pgSQL function which will simply return the results of a SELECT statement. By the way, if you find the example function useful because it gives you the most recent and previous days of the week feel free to use it in your own DB.

Happy coding! šŸ˜Ž

Categories: BlogSQL

Leave a Reply

Your email address will not be published. Required fields are marked *