Thread: Views: having a rule call a function vs. using a before trigger
Hi,
Postgresql 8.1.
I'm trying to come up with a generic way
of inserting into a view, particularly regards
error testing and the generation of complicated
foreign keys. I don't seem to be having much luck.
(I also want to update and delete, but haven't gotten
that far.)
I thought that, for inserts at least, I could write
a rule that called a function and have the function
do anything complicated, raise exceptions, etc.
But I get the error message shown below.
It occurs to me that I might be able to get
a BEFORE trigger declared on the view to
work, but that seems a bit unusual and
I was wondering if it'd even be supported.
I'd prefer to stay in the land of the sane,
so if there's just no generic technique
that lets me execute arbitrary code when
inserting/updating/deleting into views,
well, I'll just have to scale back. But I want
to explore all the options.
The basic idea for inserts is to have a rule
on the view supporting the insert that does:
DO INSTEAD
INSERT INTO underlying_table (c1, c2, ...)
SELECT n.c1, ... FROM viewinsertfunc(NEW.*)
AS n (c1, c2, ...);
viewinsertfunc() would do all the work of
error checking, foreign key generation, etc.
It would also do all the necessary inserting
into all underlying tables. Finally it
would return a SETOF record that would always
be empty. That way the DO INSTEAD INSERT
would never actaully do any inserting
and the function could do all the work,
but the INSTEAD would still be an INSERT
and thus appropriate return codes would
be supplied to any clients.
The implimentation below is a slightly modified
version of this. The function actually
returns a row to be inserted, just because
this is a simple case where there's only
one table underlying the view and nothing
much complicated is going on. In this
case I could probably get away without
having a function at all and just
use constraints on the view for error
checking and use COALESCE to come up
with the right values. I'm presenting
the simple case but I've got other views
where coming up with the right data values
involves looking at other tables and
I'd really like a function to handle
the data generation.
-----------------------------------------------------
The error message I get when I try to create the rule is:
ERROR: function expression in FROM may not refer to other relations
of same query level
-----------------------------------------------------
The table:
Table "babase.interact_data"
Column | Type |
Modifiers
--------+---------------------------+-------------------------------------------------------------
iid | integer | not null default
nextval('interact_data_iid_seq'::regclass)
sid | integer |
act | character(2) | not null
date | date | not null
start | time(0) without time zone |
stop | time(0) without time zone |
-----------------------------------------------------
The view:
View "babase.interact"
Column | Type | Modifiers
----------+---------------------------+-----------
iid | integer |
sid | integer |
act | character(2) |
date | date |
jdate | integer |
start | time(0) without time zone |
startspm | double precision |
stop | time(0) without time zone |
stopspm | double precision |
View definition:
SELECT interact_data.iid, interact_data.sid, interact_data.act,
interact_data.date, julian(interact_data.date) AS jdate,
interact_data."start", spm(interact_data."start") AS startspm,
interact_data.stop, spm(interact_data.stop) AS stopspm
FROM interact_data
ORDER BY interact_data.iid;
-----------------------------------------------------
The rule:
CREATE OR REPLACE RULE interact_insert
AS ON insert
TO interact
DO INSTEAD
INSERT INTO interact_data (iid, sid, act, date, start, stop)
SELECT n.iid, n.sid, n.act, n.date, n.start, n.stop
FROM _interact_insert(NEW.*)
AS n (iid INT
, sid INT
, act CHAR(2)
, date DATE
, start TIME(0)
, stop TIME(0));
-----------------------------------------------------
The function:
CREATE OR REPLACE FUNCTION
_interact_insert(this_row interact)
RETURNS interact_data
LANGUAGE plpgsql
AS $$
-- Handle inserts into the interact view.
--
-- GPL_notice(` --', `2007', `Karl O. Pinc <kop@meme.com>')
--
-- Syntax: _interact_insert(this_row)
--
-- Input:
-- this_row A the interact row to insert.
--
-- Returns:
-- A interact_data row to insert.
--
-- Remarks:
-- You'd think the either-data-or-computed-value code would
-- be ripe for a macro but there's just too many parameters
-- to make sense of the macro.
DECLARE
new_row interact_data%ROWTYPE;
BEGIN
-- Assign iid.
IF this_row.iid IS NULL THEN
new_row.iid := nextval('interact_data_iid_seq');
ELSE
new_row.iid = this_row.iid;
END IF;
-- Assign sid.
new_row.sid := this_row.sid;
-- Assign act.
new_row.act := this_row.act;
-- Assign date.
IF this_row.date IS NOT NULL THEN
IF this_row.jdate IS NOT NULL
AND julian(this_row.date) != this_row.jdate THEN
-- Mismatch between supplied julian date and actual date.
RAISE EXCEPTION 'INTERACT.Iid %: INTERACT.Date %:
INTERACT.Jdate: Mismatch between Date and Jdate: Cannot insert into
INTERACT_DATA'
, new_row.iid
, this_row.date
, this_row.jdate;
RETURN new_row;
END IF;
new_row.date := this_row.date;
ELSE
IF this_row.jdate IS NOT NULL THEN
-- Use the jdate when there's no date.
new_row.date := julian_to(this_row.jdate);
ELSE
-- NULL date. Bound to cause problems later.
new_row.date := this_row.date;
END IF;
END IF;
-- Assign start.
IF this_row.start IS NOT NULL THEN
IF this_row.startspm IS NOT NULL
AND startspm(this_row.start) != this_row.startspm THEN
-- Mismatch between supplied startspm and actual start.
RAISE EXCEPTION 'INTERACT.Iid %: INTERACT.Start %:
INTERACT.Startspm: Mismatch between Start and Startspm: Cannot insert
into INTERACT_DATA'
, new_row.iid
, this_row.start
, this_row.startspm;
RETURN new_row;
END IF;
new_row.start := this_row.start;
ELSE
IF this_row.startspm IS NOT NULL THEN
-- Use the startspm when there's no start.
new_row.start := spm_to(this_row.startspm);
ELSE
-- NULL start. Bound to cause problems later.
new_row.start := this_row.start;
END IF;
END IF;
-- Assign stop.
IF this_row.stop IS NOT NULL THEN
IF this_row.stopspm IS NOT NULL
AND stopspm(this_row.stop) != this_row.stopspm THEN
-- Mismatch between supplied stopspm and actual stop.
RAISE EXCEPTION 'INTERACT.Iid %: INTERACT.Stop %:
INTERACT.Stopspm: Mismatch between Stop and Stopspm: Cannot insert into
INTERACT_DATA'
, new_row.iid
, this_row.stop
, this_row.stopspm;
RETURN new_row;
END IF;
new_row.stop := this_row.stop;
ELSE
IF this_row.stopspm IS NOT NULL THEN
-- Use the stopspm when there's no stop.
new_row.stop := spm_to(this_row.stopspm);
ELSE
-- NULL stop. Bound to cause problems later.
new_row.stop := this_row.stop;
END IF;
END IF;
RETURN new_row;
END;
$$;
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
"Karl O. Pinc" <kop@meme.com> wrote:
> Hi,
>
> Postgresql 8.1.
>
> I'm trying to come up with a generic way
> of inserting into a view, particularly regards
> error testing and the generation of complicated
> foreign keys. I don't seem to be having much luck.
>
> (I also want to update and delete, but haven't gotten
> that far.)
> I thought that, for inserts at least, I could write
> a rule that called a function and have the function
> do anything complicated, raise exceptions, etc.
> But I get the error message shown below.
>
> Hi,
>
> Postgresql 8.1.
>
> I'm trying to come up with a generic way
> of inserting into a view, particularly regards
> error testing and the generation of complicated
> foreign keys. I don't seem to be having much luck.
>
> (I also want to update and delete, but haven't gotten
> that far.)
Before I mount too much of a reply, may I ask in particular>
why you are targeting views? Reason I ask is that I've gone
through the same process and have worked out a general
approach to implementing all biz logic in triggers.
However, as the biz logic is defined in terms of tables, we
attached all logic always to tables. When for whatever
reason the user is going through a view, the rule sends it
back to the base table where the biz logic takes care of it
there.
This avoids attempting to redefine or recast the rules in
the context of any particular view.
So that is why I am asking why in particular you
are asking about views.
> I thought that, for inserts at least, I could write
> a rule that called a function and have the function
> do anything complicated, raise exceptions, etc.
> But I get the error message shown below.
>