Hi Jeffrey,
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
CREATE OR REPLACE FUNCTION
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.
On 1/16/07, Jeffrey Zelt <[EMAIL PROTECTED]> wrote:
I am starting to look at the built-in Django testing framework. This
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
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django
users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
