stack depth limit exceeded

On Mon, Aug 29, 2005 at 12:01:59PM +1000, Jamie Deppeler wrote:

At the moment i am trying to execute a very simple function but i am
getting the following error stack depth limit exceeded

That's often a sign of infinite recursion, i.e., a function that
keeps calling itself, either directly or indirectly.

CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS
trigger AS
$body$
begin
update contacts.person
set "contact" = new.firstname
where person."primary" = new."primary";
return null;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

What's the trigger definition look like? I'd guess that the update
on contacts.person has a trigger that somehow gets back to this
function, which updates contacts.person, which invokes the trigger,
etc.

--
Michael Fuhr

Jamie Deppeler <jamie@doitonce.net.au> writes:

At the moment i am trying to execute a very simple function but i am
getting the following error stack depth limit exceeded

You didn't really show the complete context, but seeing that this is a
trigger and it's trying to do an "UPDATE person" internally, I'll bet
a nickel that the trigger itself is on update events on person, and
therefore that you've written an infinite recursion.

Had you shown more context, I could have given some advice on a better
way to do it. If you're trying to alter the row that's about to be
stored, you just have to assign to field(s) of the NEW row within the
trigger. If you want to do something else, you need to explain what.

regards, tom lane

What i am trying to do is update the field contact with field values in
firstname and lastname

Trigger

CREATE TRIGGER "updateContact" AFTER INSERT OR UPDATE
ON FOR EACH ROW
EXECUTE PROCEDURE "contacts"."addContactField"();

Procedure

CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS
trigger AS
$body$
begin
update contacts.person
set "contact" = new.firstname
where person."primary" = new."primary";
return null;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Tom Lane wrote:

I think that you forgot the table name.

CREATE TRIGGER "updateContact" AFTER INSERT OR UPDATE
ON contacts FOR EACH ROW
EXECUTE PROCEDURE "contacts"."addContactField"();

Frank

Jamie Deppeler wrote:

On Mon, Aug 29, 2005 at 01:45:32PM +1000, Jamie Deppeler wrote:

CREATE TRIGGER "updateContact" AFTER INSERT OR UPDATE
ON FOR EACH ROW
EXECUTE PROCEDURE "contacts"."addContactField"();

Please show the actual commands that you're running; the above fails
with a syntax error because it's missing a table name. Is this
trigger on contacts.person?

CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS
trigger AS
$body$
begin
update contacts.person
set "contact" = new.firstname
where person."primary" = new."primary";
return null;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

As Tom mentioned, if you want to modify the record being inserted
then simply assign a value to one of NEW's columns and have the
function return NEW. In such a case the function will need to be
called in a BEFORE trigger. See "Triggers" and "Trigger Procedures"
in the documentation for more information:

http://www.postgresql.org/docs/8.0/static/triggers.html
http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html

--
Michael Fuhr