I have followed up this problem and tried both the psycopg and psycopg2
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:
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
-~----------~----~----~----~------~----~------~--~---
