Views: having a rule call a function vs. using a before trigger - Mailing list pgsql-general

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