Re: rules, triggers and views

Andrew - Supernews <andrew+nonews(at)supernews(dot)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