GeoServer的不会写我的PostgreSQL更新的视图(GeoServer won't

从下面就这个问题，前面我在PostgreSQL的8.4和我有可更新视图的麻烦。

我有一个观点：

CREATE VIEW filedata_view
AS SELECT num, id, ST_TRANSFORM(the_geom,900913) AS the_geom
FROM filedata

并希望从我的应用程序抛出利用Geoserver更新。却得到了一个错误：

<ServiceExceptionReport version="1.2.0" xmlns="http://www.opengis.net/ogc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.opengis.net/ogc http://schemas.opengis.net/wfs/1.0.0/OGC-exception.xsd">
 <ServiceException> {http://www.opengeospatial.net/cite}filedata_view is read-only    </ServiceException>
</ServiceExceptionReport>

所以意见PostgreSQL中是不可更新的。我需要创建一个规则或触发更新视图。

我尝试这样做：

CREATE OR REPLACE RULE ins_view_2 AS
ON UPDATE TO filedata_view DO INSTEAD  UPDATE filedata SET the_geom=ST_TRANSFORM(NEW.the_geom,70066)
WHERE num=NEW.num

但它并没有帮助，我仍然得到同样的错误。

哪里是我的错？

Answer 1:

First, I couldn't agree more with Frank. Use 9.1, and a table trigger. However, it's possible that neither that nor a view will solve your problem.

Try doing a manual UPDATE on your view from psql. If that works, and if you connect using the same user ID with opengeospatial, then I'd say the issue could be opengeospatial being too clever for its own good and "knowing" that views can't be updated. Either that, or it's trying an INSERT and you haven't added a matching INSERT rule on your view.

The message "filedata_view is read-only" isn't a message PostgreSQL may produce. I'm wondering if opengeospatial is using JDBC metadata (assuming it's Java) or INFORMATION_SCHEMA or similar to query the schema, is determining that filedata_view is a view, and is concluding that it therefore can't update it.

If it were a message from PostgreSQL it would instead say:

# UPDATE customer_v SET customer_number = 1234; 
ERROR:  cannot update view "the_view" 
HINT:  You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.

It might be informative to enable log_statement = 'all' in postgresql.conf and reload postgresql. Re-test, then look in the logs see what exactly opengeospatial is doing.

If it turns out it's detecting a view, you might be able to work around the problem with an ON SELECT rule added to an empty table. The table will work just like a view, but GeoServer won't be able to tell it is a view and might agree to write to it.

Answer 2:

不要使用这个规则，而是触发。你至少需要9.1版本，旧版本不上的观点支持触发器。

触发器是一种数据库应自动执行每当执行一个特定类型的操作的特定功能的规范。触发器可以连接到两个表和视图。
在表中，触发器可以被定义之前或之后任何INSERT，UPDATE执行，或DELETE操作，每个SQL语句每修改行无论是一次或一次。 UPDATE触发器可以另外设置仅如果某些列UPDATE语句的SET子句中提到的火。触发器也可以火TRUNCATE语句。如果触发事件发生时，触发的函数被调用在适当的时候来处理该事件。

传闻是，规则将EOL在一段时间。