The Nth largest object

An explanation for writing functions in SQL

Photo by Nathan Anderson on Unsplash

Did you see comet NEOWISE the past few weeks? What about Jupiter and Saturn? Are you prepared to see the Perseid meteor shower this week? The night sky is always fascinating and changing.

Speaking of change, you might write the same SQL query and use it multiple times, while changing only one value. It can be time-consuming to change that value manually. I provide an automated solution in this tutorial. I will use the PostgreSQL CREATE FUNCTION statement to develop a user-defined function and apply it on one of my data tables, solar_system_20.

Information on the data table

I am going to use a table that I created called solar_system_20. This table has facts about the 20 largest objects in the Solar System, where the largest is defined by the equatorial width of the object. I’ve queried this table multiple times last month for various SQL examples in:

• Touring the Solar System with SQL
• Searching for Moons with SQL
• A window to the solar system

to show hands-on basic and intermediate examples of window and aggregate functions, subqueries, aliases, and the GROUP BY and HAVING commands in SQL.

Here’s some metadata about the table.

• solar_system_20 is a relational database table with the following columns:
• ss_name —a TEXT field that is the unique name of the Solar System object
• diameter— an INT that represents the equatorial width of the object (in miles)
• classification — a TEXT field that is the type of object in the record (like a star, planet, or dwarf planet)
• ss_location — a TEXT field that describes the location of the object in the Solar System

Creation of a function

Let’s take a look at how to create a function with PostgreSQL. Suppose we want to get the 6th largest object in thesolar_system_20table. We can use the OFFSET function to achieve this. (Note: We need to remember to subtract 1 from 6 since the table is 0 indexed.)

SELECT diameter
  FROM solar_system_20
  ORDER BY diameter 
  LIMIT 1 OFFSET 5;

Result:

Nice. But say we want to use this query over and over — in this case, we would create a function. The function I want to create is nth_largest_diameter(N) where N is 6 in the above example. The PostgreSQL call will look like this:

SELECT nth_largest_diameter(6);

Let’s examine the block of code that defines the function.

CREATE FUNCTION nth_largest_diameter(N INT)
RETURNS INT
LANGUAGE plpgsql
AS
'
  DECLARE
    N int := N - 1;
    return_value int;
  BEGIN
    SELECT diameter
    INTO return_value
    FROM solar_system_20
    ORDER BY diameter 
    LIMIT 1 OFFSET N;
    RETURN return_value;
  END;
'

Now, let’s break that code down.

• CREATE FUNCTION nth_largest_diameter(N INT): This creates the function with the name nth_largest_diameter and the arguments which are just N, an integer.
• The function returns an integer: RETURNS INT and the language is declared with LANGUAGE plpgsql.
• AS ‘ and ‘ begins and ends the calculations of the function.
• DECLARE sets the value N as an integer and calculates the value that is used in the query from the argument of the function. I also introduce the return value here and declare it as an integer.
• After DECLARE, between BEGIN and END, the query is defined as well as the return value stated. Note that I used INTO below the SELECT statement. Here, I am creating a new view where the value will be present.

So what does the result look like? Again we use SELECT nth_largest_diameter(6);, and the result is:

Which is the same as our above query.

Let's try another input, the 13th object in the table:

And to verify —

SELECT diameter
FROM solar_system_20
ORDER BY diameter 
LIMIT 1 OFFSET 12;

Result:

That’s it for this tutorial, folks! Another tool for your SQL (flavor: PostgreSQL) toolbox — creating a callable function.

For your reference, I have a series of SQL tutorials that cover basic and intermediate queries. Take a look!

• Black holes, planets, and SQL — The first tutorial that introduces SQL and covers SELECT, WHERE, and LIMIT .
• Touring the Solar System with SQL — Which covers common aggregate functions and subqueries.
• Searching for moons with SQL — Here, I cover more complex queries with aggregate functions.
• Classifying black holes with SQL — A tutorial with a CASE function.
• Joining Constellations with SQL — Where I introduce the idea of joining two tables.
• Solar System classifications with SQL — Here, I cover the array aggregate function, OFFSET, and the topic of Table vs Expanded Displays.
• Many (to many) relations among the stars — A tutorial that covers relationships in a database.
• Creating tables in SQL — Which covers two methods of creating tables.
• A window to the solar system — Covering window functions.
• LAG functionality on SQL Data and Finding seasonality in sales data — Tutorials that use the LAG and LEAD functionality.

Like these SQL posts? Have a question? Let’s chat in the comments!