Обсуждение: pl/pythonu
Dear All,
Could anyone explain why this function does will not work? The error message
is
DETAIL: exceptions.RuntimeError: maximum recursion depth exceeded.
CREATE FUNCTION testing() RETURNS trigger AS'
plan=plpy.prepare(''INSERT INTO t1 values ($1)'',[''text''])
plpy.execute(plan,[''blah''])
return ''MODIFY''
'LANGUAGE plpythonu;
Thanks
Colin
_________________________________________________________________
Sign-up for a FREE BT Broadband connection today!
http://www.msn.co.uk/specials/btbroadband
C G wrote:
> Dear All,
>
> Could anyone explain why this function does will not work? The error
> message is
> DETAIL: exceptions.RuntimeError: maximum recursion depth exceeded.
>
> CREATE FUNCTION testing() RETURNS trigger AS'
>
> plan=plpy.prepare(''INSERT INTO t1 values ($1)'',[''text''])
> plpy.execute(plan,[''blah''])
> return ''MODIFY''
>
> 'LANGUAGE plpythonu;
Do you have a trigger on 't1' which invokes testing()?
Mike Mascari
C G wrote:
>>> Dear All,
>>>
>>> Could anyone explain why this function does will not work? The error
>>> message is
>>> DETAIL: exceptions.RuntimeError: maximum recursion depth exceeded.
>>>
>>> CREATE FUNCTION testing() RETURNS trigger AS'
>>>
>>> plan=plpy.prepare(''INSERT INTO t1 values ($1)'',[''text''])
>>> plpy.execute(plan,[''blah''])
>>> return ''MODIFY''
>>>
>>> 'LANGUAGE plpythonu;
>>
>>
>> Do you have a trigger on 't1' which invokes testing()?
>>
>> Mike Mascari
>
> I do have a trigger on t1. The function loads into the database OK, but
> when I insert anything into the database I get the error, relating to
> the function.
I don't see anything that will stop the recursion:
a) You insert into t1
b) testing() gets invoked
c) testing inserts into t1
d) testing() gets invoked
e) testing inserts into t1
f) testing() gets invoked
..etc..
If all you are trying to do is modify the value that gets inserted
into t1 when an INSERT occurs, just modify the TD[''new''] record:
CREATE FUNCTION testing() RETURNS trigger AS '
TD[''new''][''field1''] = ''foo''
return ''MODIFY''
' LANGUAGE plpythonu;
where 'field1' is the field to be modified and 'foo' is the value.
HTH,
Mike Mascari
On Wednesday 11 February 2004 15:56, C G wrote:
> Dear All,
>
> Could anyone explain why this function does will not work? The error
> message is
> DETAIL: exceptions.RuntimeError: maximum recursion depth exceeded.
>
> CREATE FUNCTION testing() RETURNS trigger AS'
>
> plan=plpy.prepare(''INSERT INTO t1 values ($1)'',[''text''])
I know nothing of pl-python, but I don't suppose this is a trigger function on
table t1 is it?
Or, does a trigger on t1 activate an insert on t2, which inserts on t1,
which...
--
Richard Huxton
Archonet Ltd
> > Dear All,
> >
> > Could anyone explain why this function does will not work? The error
>message
> > is
> > DETAIL: exceptions.RuntimeError: maximum recursion depth exceeded.
> >
> > CREATE FUNCTION testing() RETURNS trigger AS'
> >
> > plan=plpy.prepare(''INSERT INTO t1 values ($1)'',[''text''])
> > plpy.execute(plan,[''blah''])
> > return ''MODIFY''
> >
> > 'LANGUAGE plpythonu;
>
>Perhaps the plpy.execute is inserting into the same table as the trigger
>is on? If that's the case, then the trigger will be recursively called
>over and over until the "maximum recursion depth" is "exceeded".
Yes, that is what is happening. My question is now, if I have a trigger on
table t1, how should I write my function to insert 'blah' into my table when
it is triggered?
Thanks
Colin
_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
On Wed, 11 Feb 2004, C G wrote:
> Dear All,
>
> Could anyone explain why this function does will not work? The error message
> is
> DETAIL: exceptions.RuntimeError: maximum recursion depth exceeded.
>
> CREATE FUNCTION testing() RETURNS trigger AS'
>
> plan=plpy.prepare(''INSERT INTO t1 values ($1)'',[''text''])
> plpy.execute(plan,[''blah''])
> return ''MODIFY''
>
> 'LANGUAGE plpythonu;
Perhaps the plpy.execute is inserting into the same table as the trigger
is on? If that's the case, then the trigger will be recursively called
over and over until the "maximum recursion depth" is "exceeded".
On Wed, 11 Feb 2004, C G wrote:
>
> > > Dear All,
> > >
> > > Could anyone explain why this function does will not work? The error
> >message
> > > is
> > > DETAIL: exceptions.RuntimeError: maximum recursion depth exceeded.
> > >
> > > CREATE FUNCTION testing() RETURNS trigger AS'
> > >
> > > plan=plpy.prepare(''INSERT INTO t1 values ($1)'',[''text''])
> > > plpy.execute(plan,[''blah''])
> > > return ''MODIFY''
> > >
> > > 'LANGUAGE plpythonu;
> >
> >Perhaps the plpy.execute is inserting into the same table as the trigger
> >is on? If that's the case, then the trigger will be recursively called
> >over and over until the "maximum recursion depth" is "exceeded".
>
> Yes, that is what is happening. My question is now, if I have a trigger on
> table t1, how should I write my function to insert 'blah' into my table when
> it is triggered?
You're likely looking for the new/old row set thing. I'm not sure how the
struct is put together in plpython. It holds the old row before the
insert, and the new row, which your trigger will actually insert in its
place.
Here's the link for rules. http://www.postgresql.org/docs/7.3/static/rules-insert.html Richard Huxton wrote: > On Wednesday 11 February 2004 15:56, C G wrote: > >>Dear All, >> >>Could anyone explain why this function does will not work? The error >>message is >>DETAIL: exceptions.RuntimeError: maximum recursion depth exceeded. >> >>CREATE FUNCTION testing() RETURNS trigger AS' >> >>plan=plpy.prepare(''INSERT INTO t1 values ($1)'',[''text'']) > > > I know nothing of pl-python, but I don't suppose this is a trigger function on > table t1 is it? > > Or, does a trigger on t1 activate an insert on t2, which inserts on t1, > which... -- Barbara E. Lindsey, COG RDC Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162 ---- CONFIDENTIALITY NOTICE: The information contained in this electronic message is legally privileged and confidential and intended only for the use of the individual(s) or entity(ies) named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this email or any of it's components is strictly prohibited. If you have received this email in error, please contact the sender. ----
You could write a rule to trigger a record into a table as well.
Richard Huxton wrote:
> On Wednesday 11 February 2004 15:56, C G wrote:
>
>>Dear All,
>>
>>Could anyone explain why this function does will not work? The error
>>message is
>>DETAIL: exceptions.RuntimeError: maximum recursion depth exceeded.
>>
>>CREATE FUNCTION testing() RETURNS trigger AS'
>>
>>plan=plpy.prepare(''INSERT INTO t1 values ($1)'',[''text''])
>
>
> I know nothing of pl-python, but I don't suppose this is a trigger function on
> table t1 is it?
>
> Or, does a trigger on t1 activate an insert on t2, which inserts on t1,
> which...
--
Barbara E. Lindsey,
COG RDC
Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162
----
CONFIDENTIALITY NOTICE: The information contained in this electronic
message is legally privileged and confidential and intended only for the
use of the individual(s) or entity(ies) named above. If the reader of
this message is not the intended recipient, you are hereby notified that
any dissemination, distribution, or copying of this email or any of it's
components is strictly prohibited. If you have received this email in
error, please contact the sender.
----
Dear All, Thanks for all the help. I now have what I want. I just couldn't figger out the syntax for the triggers in plpython, i.e. TD[''new''[[''col1'']. Easy when you know how. Thanks again Colin _________________________________________________________________ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger