pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNonexistent NEW relation in some places of rules
3+ messages / 2 participants
[nested] [flat]
* Nonexistent NEW relation in some places of rules
@ 2003-07-29 21:33 Denis Zaitsev <zzz@anda.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Denis Zaitsev @ 2003-07-29 21:33 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Nonexistent NEW relation in some places of rules
@ 2003-07-29 22:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Denis Zaitsev <zzz@anda.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2003-07-29 22:32 UTC (permalink / raw)
To: Denis Zaitsev <zzz@anda.ru>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Nonexistent NEW relation in some places of rules
@ 2003-07-30 21:02 Denis Zaitsev <zzz@anda.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Denis Zaitsev @ 2003-07-30 21:02 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-07-30 21:02 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-29 21:33 Nonexistent NEW relation in some places of rules Denis Zaitsev <zzz@anda.ru>
2003-07-29 22:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-30 21:02 ` Denis Zaitsev <zzz@anda.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox