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 onsync_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.