SQL from zero · Chapter 28 of 37

Reusable SQL

By the end of this chapter you can write your own functions in Postgres: one that returns a value, one that returns rows, and one that takes steps and raises errors. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Postgres powers
  • 4 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Meera's designs show the same label under every member's photo: Anya Sharma (@anya). The app builds that string in three places, and they do not agree on what to show for a member with no display name. She also wants the home feed and the Follow button to behave the same in the app, the admin tool and every report.

With what you know so far, you copy the expression or the query into every place that needs it, and the copies drift apart. And some jobs are steps, not one query: look up two members, stop if one does not exist, check whether the follow is already there, and only then insert. One SELECT cannot do that.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    member-label.sql: Meera's member label, as an immutable function

    Create a function member_label(p_username text, p_display_name text) that returns text: the display name, a space, and the username after an @ in brackets, like 'Anya Sharma (@anya)'. When the display name is NULL, it returns just '@' and the username, like '@yash'.

    It only looks at its arguments, so mark it IMMUTABLE.

  2. Task 2 · interview question

    Interview: post-count.sql, post_count with the right promise

    Create a function post_count(p_username text) that returns how many posts that member has that are not deleted. A member with no posts gives 0, and so does a username nobody has. Members who left still get their posts counted.

    Mark it with the right volatility, IMMUTABLE, STABLE or VOLATILE: it reads tables, and it changes nothing.

  3. Task 3

    feed.sql: a member's home feed, newest first

    Create a function feed(p_username text, p_limit integer) that returns a member's feed: the posts that are not deleted, written by the members they follow, at most p_limit of them. Posts by followed members who later left still count. A username nobody has gives no rows.

    It returns a table with three columns: author (the username of the post's author, as text), content and created_at (timestamptz). Newest post first; when two posts have the same time, by author A to Z.

  4. Task 4

    follow.sql: the Follow button, safe to press twice

    Create a PL/pgSQL function follow(p_follower text, p_followee text) that returns text. It takes two usernames and:

    raises an exception with a message starting 'no such member' when either username does not exist;

    returns 'already following' and changes nothing when the follow is already there;

    otherwise adds the follow (one new row in follows) and returns 'followed'.

What you will learn

  • SQL functions: a query with a name and arguments, called like upper() or count().
  • IMMUTABLE, STABLE and VOLATILE: the promise a function makes about what it reads and changes.
  • RETURNS TABLE: a function you select from like a table.
  • PL/pgSQL: functions with variables, IF and errors.

More in Postgres powers

Build "Reusable SQL" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.