Lists: | pgsql-bugs |
---|
From: | "Teodor Buchner" <t(dot)buchner(at)autoguard(dot)pl> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5400: Columns count mismatch in RULE with subquery |
Date: | 2010-03-31 13:21:12 |
Message-ID: | 201003311321.o2VDLCIi030298@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5400
Logged by: Teodor Buchner
Email address: t(dot)buchner(at)autoguard(dot)pl
PostgreSQL version: 8.4.1
Operating system: Linux
Description: Columns count mismatch in RULE with subquery
Details:
When SELECT INSERT syntax is used to pump data in a rule, an error appears
during creation of this rule if only SELECT has more than one argument.
ERROR: INSERT has more target columns than expressions
Stan SQL:42601
Znak:137
CREATE OR REPLACE RULE move_iteration_1 AS ON INSERT TO a.iteration DO ALSO
INSERT INTO b.iteration(id,date_period_begin)
SELECT (NEW.id,NEW.date_period_begin) FROM a.iteration;
The example is dummy as INSERT VALUES can be used here but it is impossible
to build any complex subquery instead. Reduction to a single variable
(removing date_period_begin from INSERT/SELECT) removes this error.
Rgds
TB
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Teodor Buchner" <t(dot)buchner(at)autoguard(dot)pl> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5400: Columns count mismatch in RULE with subquery |
Date: | 2010-03-31 14:35:17 |
Message-ID: | 20806.1270046117@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Teodor Buchner" <t(dot)buchner(at)autoguard(dot)pl> writes:
> CREATE OR REPLACE RULE move_iteration_1 AS ON INSERT TO a.iteration DO ALSO
> INSERT INTO b.iteration(id,date_period_begin)
> SELECT (NEW.id,NEW.date_period_begin) FROM a.iteration;
Your use of parentheses in the SELECT targetlist is incorrect (and would
be with or without the RULE context). What this is trying to do is
insert a single composite column into the target table. You want
INSERT INTO b.iteration(id,date_period_begin)
SELECT NEW.id,NEW.date_period_begin FROM a.iteration;
regards, tom lane