Thread

1. AW: [HACKERS] rules bug?

   Zeugswetter Andreas <andreas.zeugswetter@telecom.at> — 1999-04-28T08:04:00Z

   > create rule surveys_ins as on insert to surveys
   > do instead
   > insert into survey_data (survey_date, name)
   > 	select new.survey_date, new.name where not exists
   > 	(select * from survey_data d where d.survey_date = new.survey_date
   > and d.name = new.name);
   > 
   Since this is a rewrite rule, the whole statement gets rewritten, thus 
   leading to different results, when one statement inserts many rows (insert
   into ... select)
   or one statement only inserts one row (insert ...).
   
   The "problem" is visibility of data. The rows that have already been
   inserted by this 
   same statement (insert ...select) are not visible to the restricting select.
   
   Andreas 
   
   
   

2. Re: AW: [HACKERS] rules bug?

   Brook Milligan <brook@trillium.nmsu.edu> — 1999-04-28T14:19:28Z

      > create rule surveys_ins as on insert to surveys
      > do instead
      > insert into survey_data (survey_date, name)
      > 	select new.survey_date, new.name where not exists
      > 	(select * from survey_data d where d.survey_date = new.survey_date
      > and d.name = new.name);
   
      The "problem" is visibility of data. The rows that have already been
      inserted by this 
      same statement (insert ...select) are not visible to the restricting select.
   
   Thanks for the clear explanation; it makes sense now.  But ...
   
   I really need a way to enter data into a table, then disperse it among
   a bunch of others while maintaining all the correct relationships.
   Rules seem perfect for this, except for this problem.
   
   Is the only way to do this to convert the input table into a bunch of
   individual INSERT commands (one per row)?
   
   One way to do this is to use pg_dump to dump the data from the input
   table, use a script to change target table, and reload the data.
   
   Are there other better ways to do this?  other workarounds?
   
   Thanks again for your help.
   
   Cheers,
   Brook