Thread: Nonexistent NEW relation in some places of rules
In general, the task I'm trying to solve is to make a multiply inserts for a table on the one only insert for a view. It should be noted about the method of producing these multiply rows, that they depend on the VALUES given to that <top level> INSERT. So, the trivialized schema is: create function produce (text) returns setof text language plpgsql as ' begin return next $1||1; return next $1||2; return next $1||3; return; end; '; create table a (a text); create view b as select a as b from a; create rule b as on insert to b do instead insert into a select * from produce (new.b); And I get psql:zhoppa.sql:21: ERROR: Relation "*NEW*" does not exist when I feed this to psql... So, what is wrong in using NEW right from the FROM? Thanks in advance.
Denis Zaitsev <zzz@anda.ru> writes: > create rule b as > on insert to b do instead > insert into a > select * from produce (new.b); > ERROR: Relation "*NEW*" does not exist > So, what is wrong in using NEW right from the FROM? CVS tip gives a possibly more helpful error message: ERROR: function expression in FROM may not refer to other relations of same query level The problem is that the rule gets expanded into something rather like insert into a select ... from new, produce(new.b); and we can't support that. If we ever add support for SQL99's LATERAL(), it might help improve matters. regards, tom lane
On Tue, Jul 29, 2003 at 06:32:44PM -0400, Tom Lane wrote: > The problem is that the rule gets expanded into something rather like > > insert into a select ... from new, produce(new.b); > > and we can't support that. If we ever add support for SQL99's > LATERAL(), it might help improve matters. Thanks. I was suspecting something like. Does the presence of this leak mean that such a selects are needed very rarely? Or does it mean that some well-known workaround exists?