Andrew - Supernews <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
