Views

For more info on Postgres views, see the official Postgres docs. Effectively, views are named queries which can be accessed as if they were regular database tables.

Quickstart

Given the following view in SQL:

CREATE OR REPLACE VIEW myapp_viewname AS
SELECT * FROM myapp_table WHERE condition;

You can create this view by just subclassing django_postgres.View. In myapp/models.py:

import django_postgres

class ViewName(django_postgres.View):
    projection = ['myapp.Table.*']
    sql = """SELECT * FROM myapp_table WHERE condition"""

View

class django_postgres.View

Inherit from this class to define and interact with your database views.

You need to either define the field types manually (using standard Django model fields), or use projection to copy field definitions from other models.

sql

The SQL for this view (typically a SELECT query). This attribute is optional, but if present, the view will be created on sync_pgviews (which is probably what you want).

projection

A list of field specifiers which will be automatically copied to this view. If your view directly presents fields from another table, you can effectively ‘import’ those here, like so:

projection = ['auth.User.username', 'auth.User.password',
              'admin.LogEntry.change_message']

If your view represents a subset of rows in another table (but the same columns), you might want to import all the fields from that table, like so:

projection = ['myapp.Table.*']

Of course you can mix wildcards with normal field specifiers:

projection = ['myapp.Table.*', 'auth.User.username', 'auth.User.email']

Primary Keys

Django requires exactly one field on any relation (view, table, etc.) to be a primary key. By default it will add an id field to your view, and this will work fine if you’re using a wildcard projection from another model. If not, you should do one of three things. Project an id field from a model with a one-to-one relationship:

class SimpleUser(django_postgres.View):
    projection = ['auth.User.id', 'auth.User.username', 'auth.User.password']
    sql = """SELECT id, username, password, FROM auth_user;"""

Explicitly define a field on your view with primary_key=True:

class SimpleUser(django_postgres.View):
    projection = ['auth.User.password']
    sql = """SELECT username, password, FROM auth_user;"""
    # max_length doesn't matter here, but Django needs something.
    username = models.CharField(max_length=1, primary_key=True)

Or add an id column to your view’s SQL query (this example uses window functions):

class SimpleUser(django_postgres.View):
    projection = ['auth.User.username', 'auth.User.password']
    sql = """SELECT username, password, row_number() OVER () AS id
             FROM auth_user;"""

Creating the Views

Creating the views is simple. Just run the sync_pgviews command:

$ ./manage.py sync_pgviews
Creating views for django.contrib.auth.models
Creating views for django.contrib.contenttypes.models
Creating views for myapp.models
myapp.models.Superusers (myapp_superusers): created
myapp.models.SimpleUser (myapp_simpleuser): created
myapp.models.Staffness (myapp_staffness): created

Migrations

Views play well with South migrations. If a migration modifies the underlying table(s) that a view depends on so as to break the view, that view will be silently deleted by Postgres. For this reason, it’s important to run sync_pgviews after migrate to ensure any required tables have been created/updated.