postgres. plpgsql stack depth limit exceeded

Ok, if you really want the trigger on update, what you could do it set this trigger as column specific, so that it is not fired on an update to all_books, which is causing your recursion. Something like this -

create trigger total2
after update of copy_id
on totalbooks
for each row
execute procedure total1();

Of course, you can change which columns trigger the function, I just chose copy_id because that is what you are counting.

HOWEVER

If you are updating with a count() result, you can just put the trigger on INSERT and DELETE actions. This way the trigger will fire when the count changes, but will not itself be triggered by the update. // EDIT: Since your sum is only a count of all records in copies, it will only change when a record is inserted or updated, so running this trigger on update would not make sense anyway.

EDIT: I figured it would be useful to add a link to the CREATE TRIGGER Documentation. See the section labeled "event", because this details how to specify columns in the event.

EDIT FOR NEW INFORMATION:

Given what it sounds like you need to accomplish, I think you need to rethink your data design, I suggest you use a parent-child relationship (Anytime you are caching shared data on many rows in a table because they share something in common, that is a sign that you might need a parent table instead).

Have a books table where each row is information about one book (title, author, etc), and then have a copies table where each row holds information about one copy of a book (serial number, last checked out, etc).

That way, getting the count of copies is as simple as SELECT COUNT(*) FROM copies WHERE book_id=[some book id].

If you really want to cache the count somewhere, do it on the books table.

Create an INSERT OR UPDATE trigger on copies that does UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=NEW.book_id) WHERE id=NEW.book_id.

Then create a DELETE trigger on copies that does UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=OLD.book_id) WHERE id=OLD.book_id

The reason for two triggers is that the NEW variable is only available in INSERT or UPDATE triggers, and OLD is only available in DELETE triggers. You could do it all as one trigger, but that requires more code than I wanted to put here.

Make sure all your triggers are AFTER triggers, or else a newly inserted/deleted row won't be considered in the count.