Thread: Rules, Select, Union

I'm doing this:
 CREATE TABLE data ( val int4 ); CREATE TABLE pos ( ) INHERITS ( data ); CREATE TABLE neg ( ) INHERITS ( data );
 CREATE RULE data_to_pos AS ON INSERT TO data WHERE NEW.val > 0 DO INSTEAD   INSERT INTO pos ( val ) VALUES ( NEW.val
);
 CREATE RULE data_to_neg AS ON INSERT TO data WHERE NEW.val < 0 DO INSTEAD   INSERT INTO neg ( val ) VALUES ( NEW.val
);

So far, I think, so good.  (Though, sometimes it looks like the second
rule is never triggered even though val < 0.  The real application
that this simple example comes from has a bunch of triggers, though,
so it could be some weird interaction...)  Now I want to do:
 CREATE RULE data_val AS ON SELECT TO data DO INSTEAD   SELECT * FROM pos   UNION   SELECT * FROM neg;

Can this be done?  I'm not having much success...

This is 7.0.2...

-itai

Itai Zukerman <zukerman@math-hat.com> writes:
>   CREATE RULE data_val AS
>   ON SELECT TO data
>   DO INSTEAD
>     SELECT * FROM pos
>     UNION
>     SELECT * FROM neg;

IIRC, UNION doesn't work in rules at the moment (a fix is planned for
7.2).  But what's the point of splitting the data into the two tables
like that?  This example is far from compelling, so I wonder whether
you shouldn't be considering a schema redesign anyway ...
        regards, tom lane