jprokay

What is a View?

If you primarily use Object Relational Mapping (ORM) frameworks for interacting with your database, you may not be familiar with the concept of a View and why you would want to use it.
First, let's look at the official documentation.

CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.
PostgreSQL 16 Documentation

At a basic level, a View is a "pre-defined query" that you interact with like a table. Views are a convenient way to collect raw data from multiple tables (or just one) and format it into useful information.

Instead of relying on application code to format data - consider using a View. By moving your logic closer to the data, you simplify your application code and centralize it for easy reuse across other applications.

Creating a View

Creating a basic View is really as simple as defining a select query. This example is simple, but anything you can do with a SELECT query, you can use in a View.

          
    create table artists (
    id         bigint primary key generated always as identity,
    first_name text not null,
    last_name  text not null 
    );

    create table albums (
    id        bigint primary key generated always as identity,
    name      text not null,
    artist_id bigint not null references artist (id)
    );

    create view artist_albums as 
    select artist.first_name  as artist_first_name,
           artist.last_name   as artist_last_name,
           artist.id          as artist_id,
           album.id           as album_id,
           album.name         as album_name
    from  artists artist
    join  albums album
    using (id);

SELECT from a View

Easy.

          
    select * from artist_albums;

INSERT INTO a View

Performing INSERT operations are where Views begin to differ from a Table. For a Simple View, everything works as you would expect.

Unfortunately, the View defined in the first example is not considered a Simple View because of the JOIN clause.

Thus, INSERT, UPDATE, and DELETE statements cannot be used on the View. Create a function that returns a trigger that contains the logic to perform the insert/update/delete, and then a define a trigger to overwrite the default behavior.


          
    -- create a function to be used in a trigger when an insert occurs
    create or replace function insert_artist_albums()
    returns trigger
    language plpgsql
    as $$
    begin
    -- create an artist and store into a temporary table called artist
    with artist as (
      insert into artists (first_name, last_name)
      -- new represents the values from the view
      select new.artist_first_name, new.artist_last_name 
      on conflict do nothing
      returning *
    )

    insert into albums (name, artist_id)
    -- now we can get the id from the newly created artist
    select new.album_name, (select id from artist)
    -- always think about your strategy for handling conflicts
    on conflict do nothing;

    return new;
    end;
    $$;

    create trigger insert_artist_albums_trigger
    -- this is the important line. runs the function on insert
    instead of insert on artist_albums
    for each row execute function insert_artist_albums();

    -- example
    insert into artist_albums
    (artist_first_name, artist_last_name, album_name)
    VALUES ('foo', 'bar', 'baz');

Writing your first few functions and triggers takes practice, but they are not much different than what you would do in application code.

In the above example, we have two clear steps to the function: insert a new artist, then insert the album. Just like in application code, we must store the artist we create into a "temporary" variable using a WITH clause in order to reference the newly created artist ID.

Views & Row Level Security

Covering the details of Row Level Security (RLS) is outside the scope of this post. Check out Supabase's Guide on RLS for an overview.

By default, Views do not respect the RLS policies of underlying Tables. Add the SECURITY INVOKER = true clause when creating a View to ensure your RLS policies work as expected. This only works in Postgres 15 and above. Earlier versions of Postgres require you to implement a function.

Why use Views?

The biggest benefit I have seen since using Views is the separation of data from information. This split allows me to evolve the Table schemas while maintaining consistency at the View level.

Consider a change to the name of a column. If you change a column name, now you need to update all your application code to respect the new column name. If you have a View that defines an alias for the column using an AS clause, you only need to update the any references to the column within the View and any related functions.

A pattern I use to evolve my Views is to define versions schemas like v1 and v2. Tables stay in schemas related to their domain, while Views exist under a version schema. "Major" updates to a View, that would break existing integrations, requires the creation of new View under a later version.

Connected clients can gradually adopt the new version of the View - no different than a well-versioned API.

The more integrations you have to your database, the more value Views provide. Retool, your backend, data analysts, etc., all benefit from well defined Views.

Primer on Postgres Views