From: "Mujdat Pakkan" <mpakkan@pacbell.net>
Subject: rules and return values question
Date: Wed, 26 Nov 2003 11:29:33 -0800
Organization: Hub.Org Networking Services
Lines: 41
Message-ID: <bq2ure$ghs$1@news.hub.org>
To: pgsql-sql@postgresql.org

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?