Re: rules, triggers and views

Andrew - Supernews <andrew+nonews@supernews.com> writes:
> Use two tables. One has rules, the other has the trigger. (Neither store
> any data.) Here's a working example (tested on 7.4.5):

> -- declare the table which we're going to be manipulating. This never
> -- actually stores anything (it becomes a view).
> create table realtable (id integer, value text);

> -- This is where the data comes from (hardcoded for example purposes)
> create or replace function datasource() returns setof realtable as ...

[ this rule converts the table into a view: ]
> create rule "_RETURN" as
>    on select to realtable
>    do instead select * from datasource();

Interesting hack.  It creates a situation that CVS-tip pg_dump can't
handle:

$ pg_dump circle >circle.sql
pg_dump: [sorter] WARNING: could not resolve dependency loop among these items:
pg_dump: [sorter]   FUNCTION datasource  (ID 19 OID 293177)
pg_dump: [sorter]   TABLE TYPE realtable  (ID 206 OID 293173)
pg_dump: [sorter]   TABLE realtable  (ID 1162 OID 293172)
pg_dump: [sorter]   RULE _RETURN  (ID 1225 OID 293185)
$ 

because pg_dump isn't smart enough to break a view down into a table and
rule, which seems the only way to declare such a thing.

I'm inclined to think that it's too late to consider fixing this for 8.0
and we should leave a pg_dump fix for 8.1.

In the meantime, it might be better to avoid the circularity, like so:

create type datasource_type as (...);

create or replace function datasource() returns setof datasource_type as ...

create view realtable as select * from datasource();
        regards, tom lane