# SQL built-in functions: getting a random voter

Most SQL database systems provide functions you can run inside a query to perform all sorts of things.

For example, in PostgreSQL you can do this:


And that gives you a random number between 0.0 and 1.0[1].


We can also use the function, COUNT, to tell us how many rows for the column id exist in polls[2].

You can use COUNT with any row result. For example, COUNT(*) is totally valid and is normally the way the function is used.

Other popular aggregate functions are:

  • MAX()
  • MIN()
  • AVG()
  • SUM()

You can also create your own functions (opens new window) using SQL (in a very similar way to what we'd do using Python). However, that is a very advanced topic and we won't be looking into it in this course.

However do remember that the reason to define SQL functions is the same as for Python functions: to organise and reuse code. If you find yourself repeating yourself often within a database, you can create a function that will stay in the database and can be used in your queries. Similarly if you create a Python function or variable to execute or store a query definition, sometimes it can be useful to do this in the database instead!

# Using functions in our app queries

We'll need to use a function in our app. That's because in order to get a random person who voted for an option (i.e. the "winner"), we'll use the RANDOM function.

This is how you select a random row from a table in PostgreSQL. We're adding this to database.py:


Doing ORDER BY RANDOM() shuffles the rows. Then doing LIMIT 1 just gets the first one, after they've been shuffled.


Remember though that this query needs a value to be passed to it.

Let's define our get_random_poll_vote() function too:


def get_random_poll_vote(connection, option_id):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(SELECT_RANDOM_VOTE, (option_id,))
            return cursor.fetchone()

  1. Mathematical functions in PostgreSQL (Official Documentation) (opens new window) ↩︎

  2. Aggregate functions in PostgreSQL (Official Documentation) (opens new window) ↩︎