Thread: AW: [HACKERS] rules bug?

> 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 

> 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