pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrules and return values question
2+ messages / 2 participants
[nested] [flat]
* rules and return values question
@ 2003-11-26 19:29 Mujdat Pakkan <mpakkan@pacbell.net>
0 siblings, 1 reply; 2+ messages in thread
From: Mujdat Pakkan @ 2003-11-26 19:29 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: rules and return values question
@ 2003-11-28 21:04 Rod Taylor <pg@rbt.ca>
parent: Mujdat Pakkan <mpakkan@pacbell.net>
0 siblings, 0 replies; 2+ messages in thread
From: Rod Taylor @ 2003-11-28 21:04 UTC (permalink / raw)
To: Mujdat Pakkan <mpakkan@pacbell.net>; +Cc: pgsql-sql
> 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.
Can't you use erreport() to return an exception message?
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-11-28 21:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-26 19:29 rules and return values question Mujdat Pakkan <mpakkan@pacbell.net>
2003-11-28 21:04 ` Rod Taylor <pg@rbt.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox