The following bug has been logged online:
Bug reference: 4141
Logged by: Mathias Seiler
Email address: mathias.seiler@gmail.com
PostgreSQL version: 8.3.1
Operating system: Ubuntu: 2.6.20-16-server #2 SMP Tue Feb 12 05:48:21 UTC
2008 i686 GNU/Linux
Description: Aliases in rules vanished, but they work
Details:
Please consider the following:
postgres=# CREATE table foo (
postgres(# id serial,
postgres(# bar text
postgres(# );
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial
column "foo.id"
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+----------+-------
public | foo | table | root
public | foo_id_seq | sequence | root
(2 rows)
postgres=# CREATE VIEW foo_view AS
postgres-# SELECT * from foo limit 10;
CREATE VIEW
postgres=# \d foo_view
View "public.foo_view"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
bar | text |
View definition:
SELECT foo.id, foo.bar
FROM foo
LIMIT 10;
postgres=# CREATE RULE foo_delete AS ON DELETE TO foo_view do instead
postgres-# DELETE FROM foo f where f.id = old.id;
CREATE RULE
postgres=# \d foo_view
View "public.foo_view"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
bar | text |
View definition:
SELECT foo.id, foo.bar
FROM foo
LIMIT 10;
Rules:
foo_delete AS
ON DELETE TO foo_view DO INSTEAD DELETE FROM foo
WHERE f.id = old.id
postgres=# SELECT version();
version
----------------------------------------------------------------------------
-------------------
PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
(Ubuntu 4.1.2-0ubuntu4)
(1 row)
postgres=#
I used an alias to reference "foo" in the rule definition. Here's the
problem: If you dump this view (with rules) you get the same as above, a
"DELETE FROM foo WHERE ...": the alias is missing. As a result, the dump is
corrupted and produces errors while importing it, saying: "missing
FROM-clause entry for table "f" ...".
The view rule works btw. It deletes all rows in "foo_view" from the table
foo as expected and without any warnings or error messages.
If I should provide more details about my environment (which is pretty
default), let me know.