Learn how you can use stored procedures to give a name to any of your SELECT queries and make them reusable.

If you work with any SQL database, you might find yourself typing the same SELECT queries very often. Sometimes they are short and don’t take a lot of time to create. The other times, there’s a couple of joins, aggregation, order and you might not want to lose that function when you close the query console.

It’s very convenient to save your SELECT under any name you like and use it later to get immediate results.

Saving a simple SELECT query as a function

For demonstration purposes, let’s imagine you have the table app_user which stores the users of your application.

If you want to get the total count of your users, you’ll write something like

SELECT count(*) FROM app_user;

To give this query a name, you can save it as a stored function.

create or replace function count_app_users()
    returns table
            (
                total bigint
            )
    language plpgsql
as
$$
begin
    return query (
      select count(*) from app_user
    );
end;
$$;

Let’s break it down in detail:

  • In the first line we declare a function and give it the name count_app_users.
  • create or replace statement is used in case you already have this function and want to update it. If you’re sure it doesn’t exist, you can just use create
  • Then we explicitly define the return type, which is table for all SELECT queries
  • In the brackets, we define the columns of the return table and their types.
  • You can consider language plpgsql and the next couple of lines as a boilerplate
  • The query is placed inside of the return query(...) statement which is wrapped between $$ begin and end $$

Calling the named SELECT query

After we’ve created a function, we can call it by its name.

SELECT * FROM count_app_users();

Such syntax is determined by the fact that our function returns a table. All we do is select everything that this table has in it.

Passing arguments into the function

Now, let’s say that we want to count only the users created after a specific date.

To do that, we need to use the parameter inside of the SELECT query. In the function signature, we’ll add the parameter names and types. Then, inside of the SELECT, we can refer to the provided values.

create or replace function count_app_users(after timestamp) -- declaring the "after" parameter
    returns table
            (
                total bigint
            )
    language plpgsql
as
$$
begin
    return query (
      select count(*) from app_user
      where created_at > after                              -- using the "after" parameter
    );
end;
$$;

Now, you can add a date to the function call and get a count of users created after a specific date.

SELECT * FROM count_app_users('2020-11-01');

Deleting a stored function in PostgreSQL

If you try to call the function count_app_users without any arguments, it will still work. This happens because the function is determined not only by its own name but the parameter names as well.

So, now we have two functions count_app_users and count_app_users(after) stored in the database.

If you don’t need one of them, you can drop it like this:

DROP function count_app_users();

The syntax is similar to dropping a table and is very straightforward.

Retrieving a stored function in PostgreSQL

Our count example is oversimplified, but if you store a longer function, you might want to get the content of the function to edit it in the future.

To do this, you just need to know the name of the function

SELECT prosrc
FROM pg_proc
WHERE proname = 'count_app_users';

This returns all the text between begin and end.

Dealing with common errors

The first error that you might get when saving the SELECT query into a function looks something like this

ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Where: PL/pgSQL function count_app_users() line 3 at SQL statement

Most likely this happened because you forgot to wrap the SELECT query in the return query(...) statement.

Then, you might see the error which is related to the way you’re calling the stored function in PostgreSQL

ERROR: cannot use RETURN QUERY in a non-SETOF function

Probably you’ve tried to call the function like

call count_app_users();

or

SELECT count_app_users();

As the function returns a table and not a single value, you should call it the same way as you would select the data from any other table

SELECT * FROM count_app_users();

Conclusion

It’s very convenient to be able to give names to your SELECT queries and save them.

We’ve covered a very simple example, but you’re not limited in any way in the complexity of the SELECT that you want to save.

Use joins, aggregations, ordering, and filtering, and never have to write the same queries over and over again!