Skip site navigation (1) Skip section navigation (2)

"disappearing" rows in temp table, in recursing trigger

From: "Eric Worden" <worden(dot)eric(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: "disappearing" rows in temp table, in recursing trigger
Date: 2008-12-27 03:15:44
Message-ID: 569f05d30812261915j73b3fb13g81e519294cc00430@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Hello, I'm guessing the rows aren't really disappearing but how else
to describe it?....

 I have a trigger function that calls another function that is
recursive.  The recursive function creates a temp table and inserts
rows into it.  After the recursive function returns, the trigger
function examines the temp table in order to validate the data in it.
This all works perfectly well when the trigger function is written
slightly modified as a regular function.  However when run as a
trigger, the temp table comes back empty.  No errors are thrown.  I
have version 8.1.10.  I've tried to include the relevant parts below.
Any smarty out there see the problem?  --Eric

--=========
  --The trigger is like this:
   CREATE TRIGGER trigger_name AFTER INSERT OR UPDATE ON table_name
FOR EACH ROW EXECUTE PROCEDURE trigger_func()

--========
--The trigger_func() (abbreviated):
begin
   perform recursive_func(new.id, 1, new.id);
   l_row_count := count(*) from tmp_ancestors;
   raise debug 'total rows=%', l_row_count; --LOG OUTPUT SAYS: "total rows=0"
   ....
   return new;
end;

========
--The recursive_func():
begin
      ....
      insert into tmp_ancestors (blah, blah)...
      if logic then
         new_level := p_level + 1;
         perform recursive_func(id, new_level, id);
      end if;
   l_row_count := count(*) from tmp_ancestors;
   raise debug 'returning p_level=%; rows in tmp_ancestors=%',
p_level, l_row_count;
   --LOG OUTPUT SHOWS EXPECTED INCREMENTING NUMBERS
   return;
end

Responses

pgsql-general by date

Next:From: J OtteryDate: 2008-12-27 05:47:39
Subject: Bind message has 6 results formats but query has 5 columns
Previous:From: Tom LaneDate: 2008-12-27 00:13:48
Subject: Re: WITH AS vs subselect was: count (DISTINCT expression [ , ... ] ) and documentation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group