By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,299 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

rules and return values question

P: n/a
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?


Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.