rules and return values question

From: "Mujdat Pakkan" <mpakkan(at)pacbell(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: rules and return values question
Date: 2003-11-26 19:17:06
Message-ID: bq2u43$d6o$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

Browse pgsql-general by date

  From Date Subject
Next Message greg 2003-11-26 19:46:42 GnuPG / PGP signed MD5 and SHA1 checksums for PostgreSQL 7.4.0
Previous Message Tom Hebbron 2003-11-26 18:35:05 aggregate generic ANYARRAY