title: postgres. plpgsql stack depth limit exceeded
body: <p>im working on a simple function where it automatically updates something from a table.</p>
  
  <pre><code>create or replace function total() 
  returns void as $$
  declare
    sum int;
  begin
    sum = (SELECT count(copy_id) FROM copies);
      update totalbooks
      set all_books = sum
      where num = 1;
    end;
  $$ language plpgsql;
  </code></pre>
  
  <p>if i execute "select total();" it works perfectly fine so i made a function trigger so that it automatically updates:</p>
  
  <pre><code>create or replace function total1() returns trigger as $$
  begin
     perform (select total());
      return null;
  end;
  $$ language plpgsql;
  </code></pre>
  
  <p>but after i execute this:</p>
  
  <pre><code>create trigger total2
  after update
  on totalbooks
  for each row
  execute procedure total1();
  </code></pre>
  
  <p>it gives me an error message:</p>
  
  <pre><code>ERROR:  stack depth limit exceeded
  HINT:  Increase the configuration parameter "max_stack_depth" (currently 3072kB), after   ensuring the platform's stack depth limit is adequate.
  CONTEXT:  SQL statement "SELECT (SELECT count(copy_id) FROM copies)"
  PL/pgSQL function total() line 5 at assignment
  SQL statement "SELECT (select total())"
  PL/pgSQL function total1() line 3 at PERFORM
  SQL statement "update totalbooks
  set all_books = sum
  where num = 1"
  PL/pgSQL function total() line 6 at SQL statement
  SQL statement "SELECT (select total())"
  PL/pgSQL function total1() line 3 at PERFORM
  SQL statement "update totalbooks
  set all_books = sum
  where num = 1"
  PL/pgSQL function total() line 6 at SQL statement
  SQL statement "SELECT (select total())"
  PL/pgSQL function total1() line 3 at PERFORM
  SQL statement "update totalbooks
  set all_books = sum
  where num = 1"
  PL/pgSQL function total() line 6 at SQL statement
  SQL statement "SELECT (select total())"
  PL/pgSQL function total1() line 3 at PERFORM
  SQL statement "update totalbooks
  set all_books = sum
  where num = 1"
  PL/pgSQL function total() line 6 at SQL statement
  SQL statement "SELECT (select total())"
  PL/pgSQL function total1() line 3 at PERFORM
  SQL statement "update totalbooks
  set all_books = sum
  where num = 1"
  PL/pgSQL function total() line 6 at SQL statement
  SQL statement "SELECT (select total())"
  PL/pgSQL function total1() line 3 at PERFORM
  SQL statement "update totalbooks
  set all_books = sum
  where num = 1"
  PL/pgSQL function total() line 6 at SQL statement
  SQL statement "SELECT (select total())"
  </code></pre>
  
  <p>Obviously theres something wrong with my trigger. Please help.</p>
  
  <p>I am using Postgres 9.2.4, compiled by Visual C++ build 1600, 64-Bit</p>
  
  <p>EDIT:</p>
  
  <p>i tried the pg_trigger_depth(), but now trigger doesn't automatically update?? i still have to execute 'select total()'</p>
  
  <p>here's my new code:</p>
  
  <pre><code>create or replace function total() 
  returns void as $$
  declare
    sum int;
  begin
     sum = (SELECT count(copy_id) FROM copies);
      update totalbooks
      set all_books = sum;
  end;
  $$ language plpgsql;
  
  
  create or replace function total1() returns trigger as $$
  begin
    perform (select total());
    return null;
  end;
  $$ language plpgsql;
  
  create trigger total2
  after update
  on totalbooks
  for each row
  WHEN (pg_trigger_depth()=0)
  execute procedure total1();
  </code></pre>