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.
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
import django_postgres class ViewName(django_postgres.View): projection = ['myapp.Table.*'] sql = """SELECT * FROM myapp_table WHERE condition"""
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
projectionto copy field definitions from other models.
The SQL for this view (typically a
SELECTquery). This attribute is optional, but if present, the view will be created on
sync_pgviews(which is probably what you want).
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']
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
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
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
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
$ ./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
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
migrate to ensure any required tables have been