pl/pythonu

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

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

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

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

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

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

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.

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