How to create back-end PostgreSQL SQL triggers and stored functions?
Jeffrey Zelt
has forced me to take a new look at how I should handle SQL triggers and
stored functions. I am using *PostgreSQL* for the DB.
Django supports the automatic execution of SQL found in any files named:
<appname>/sql/<modelname>.sql
or
<appname>/sql/<modelname>.<backend>.sql
This is fine for providing initial table data and other simple
initialization requirements. However, it does not seem to allow the
creation of SQL triggers or more general stored functions with
PostgreSQL. I have tried this and it simply does not work because each
PostgreSQL function bodies is defined as a long, multi-line
(dollar-quoted) string. PostgreSQL's own client application, "psql",
handles this fine, but the JDBC connection that Django uses for
executing the SQL in the files mentioned above chokes when it tries to
process such dollar-quoted strings. I am in no way an expert on such
ODBC connections, but I have encountered similar complaints by other
PostgreSQL users in various web postings without coming across a
practical solution.
As a result, I keep all my trigger and function definitions in a
separate file that I manually feed to the database using "psql" whenever
I need to re-initialize the database. This is not a big deal and I have
gotten used to it, but this technique is not "compatible" with Django's
testing framework.
The incompatibility stems from the fact that the testing framework
automatically creates a test database for you and then drops it after
testing. When it creates the test database, it runs the initialization
files mentioned above, but there is no "hook" for it to somehow process
PostgreSQL trigger/function definitions (as dollar-quoted strings). As
a result, the test database will have the initial data defined, but no
triggers or stored functions.
Does anyone know how to package such PostgreSQL trigger/function
definitions in the initialization files mentioned above, so that Django
will automatically create them for you (i.e., during "syncdb")?
Or does Django have another mechanism for automating the creation of
backend triggers/functions that I am not aware of?
Any comments or suggestions would be appreciated.
Jeff
telenieko
I use the <appname>/sql/<modelname>.sql approach to create stored procedures and views without any problem:
i.e, on sql/MyModel.postgresql_psycopg2.sql
myschema.my_function (
input_cuenta integer,
input_fecha date,
OUT id integer,
OUT texto text,
)
RETURNS SETOF RECORD
AS $$
SELECT id, texto, some_other_procedure($1)
FROM (SELECT id, texto
FROM myschema.sometable
ORDER BY id) thistable
WHERE $1 IN (id);
$$ LANGUAGE sql;
As you see I use psycopg2 and it works like a charm, my only issue is to create pythonu prodecures but it's a postgresql issue (something to do with security) but as you see, that works.
NOTE: I've stripped almost the full code of the example (it's a long long procedure) and I've not checked the syntax on this abreviated version, but anyway, the $$ work ;)
Hope this helps,
Marc.
Jeremy Dunck
> Does anyone know how to package such PostgreSQL trigger/function
> definitions in the initialization files mentioned above, so that Django
> will automatically create them for you (i.e., during "syncdb")?
>
See telenieko's response, but in general, django uses signals for this
sort of thing. Django.db.models.signals.post_syncdb is the one you
care about. This is how the auth app offers to create a superuser
after it is first installed. See django.contrib.auth.management for
the connection example. It's dispatched from django.core.management.
Lisa
telenieko skrev:
> As you see I use psycopg2 and it works like a charm, my only issue is to
> create pythonu prodecures but it's a postgresql issue (something to do with
> security) but as you see, that works.
I am using psycopg, not psycopg2. Could that be the difference? I
wouldn't have thought so, but I will have to try and find out. Thanks.
Jeff
Jazz
database adaptors for PostgreSQL. Neither allows functions to be
defined via dollar-quoted strings. I don't understand how "telenieko"
was able to get this to work.
I placed the following code into an SQL initialization function for a
model named Subscription, subscription.postgresql_psycopg2.sql :
CREATE OR REPLACE FUNCTION django.cancel_subscriptions_to_cancel()
RETURNS void AS $$
DECLARE
STATUS_ID_ACTIV CONSTANT integer := 1;
STATUS_ID_AVBESTILT CONSTANT integer := 2;
BEGIN
UPDATE spor_subscription SET status_id=STATUS_ID_AVBESTILT
WHERE
status_id <> STATUS_ID_AVBESTILT AND CURRENT_DATE >
cancel_after_date;
RETURN;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;
I then executed $ python manage.py syncdb. The result was:
.
.
.
Installing initial data for spor.Orderable_item model
Installing initial data for spor.Boilerplate model
Installing initial data for spor.Subscription_status model
Installing initial data for spor.Document_type model
Installing initial data for spor.Subscription model
Failed to install initial SQL data for spor.Subscription model:
unterminated dollar-quoted string at or near "$$
DECLARE
STATUS_ID_ACTIV CONSTANT integer := 1;" at character 552
Installing initial data for spor.Language model
Installing initial data for spor.Country model
Installing index for admin.LogEntry model
Installing index for spor.Invoice model
.
.
.
As you can see, it triggers an "unterminated dollar-quoted string"
error. After further investigation, this seems to be related to ticket
#3214, which is so far unresolved.
I should mention that I can paste the above code into the PostgreSQL
client "psql" with no errors.
Can anyone guess why this works OK for "telenieko", but not for me?
Confused,
Jeff
telenieko skrev:
Jeremy Dunck
....
> As you can see, it triggers an "unterminated dollar-quoted string"
> error. After further investigation, this seems to be related to ticket
> #3214, which is so far unresolved.
Yeah, the code that's breaking is in django.core.management; it's not
backend specific, though the reason it's breaking is statement
splitting since "# Some backends can't execute more than one SQL
statement at a time".
As a quick hack to get going, the attached may work for you. It just
runs the whole file as a single .execute rather than fiddling with
multiple statement splitting.
But the short version is that get_sql_initial_data_for_model is not
written with languages other than pure SQL in mind.
...
> Can anyone guess why this works OK for "telenieko", but not for me?
No. :-/
Jazz
misunderstood anything. Let's hope it is eventually handled properly
by Django.
But I would rather see Django v1.0 first! :-)