rules and return values question
От | Mujdat Pakkan |
---|---|
Тема | rules and return values question |
Дата | |
Msg-id | bq2u43$d6o$1@news.hub.org обсуждение исходный текст |
Список | pgsql-general |
We have an interesting case where we want to use Postgres both as a database and a front end to a proprietary database. For the latter, we wrote functions that access the proprietary database. Then we defined views on the proprietary database and wrote rules for insert/update/delete on those views using the functions. The problem is that we cannot find a way to return reasonable error values from the access functions. The rules always return the result of the last query executed and 0 if the query is not of the same type as the original query in the rule. CREATE FUNCTION old_db_view_func(int, int, int) RETURNS int AS '...', 'old_db_view_func' LANGUAGE 'C' VOLATILE STRICT; // this accesses the proprietary database and retrieves all rows CREATE FUNCTION old_db_del_func(int) RETURNS int AS '...', 'old_db_del_func', LANGUAGE 'C' VOLATILE STRICT; // this accesses the proprietary database and deletes a row CREATE VIEW old_db_view AS SELECT t.a, t.b, t.c FROM old_db_view_func() AS t; CREATE TABLE old_db_log (op text, a int, b, int, c int, res int); CREATE RULE old_db_del AS ON DELETE TO old_db_view DO INSTEAD INSERT INTO old_db_log VALUES ('delete', OLD.a, OLD.b, OLD.c, old_db_del_func(OLD.a)); Now when you execute: DELETE FROM old_db_view WHERE a=1; you always get 0 as a result whether there is a row with a=1 or not, since the last query is an INSERT (into old_db_log) and is not of the same type as the original query (DELETE). And even if you do an INSERT INTO old_db_view VALUES (1,2,3); where there already exists a row with a =1 and the function returns an error, the rule returns 1. We do not really need the old_db_log but you cannot invoke functions directly from the rules. You need to give a query. We could have dummy tables but it introduces too much overhead. In any case, we need the return value (or an exception) from the function but there does not seem a way to return it. Anybody had a similar requirement before?
В списке pgsql-general по дате отправления: