Re: can a trigger on insert -> update other tables? - Mailing list pgsql-general

On Sun, Mar 04, 2001 at 04:52:16AM -0500, Jan Wieck wrote:
> will trillich wrote:
> > [...]
> >
> > CREATE TRIGGER _rating_propagate
> >    BEFORE INSERT ON _rating
> >    FOR EACH ROW EXECUTE PROCEDURE _rating_propagate();
> >
> > ERROR: NEW used in non-rule function
> >
> > (this doesn't happen until an insert actually activates the
> > trigger, so that plpgsql finally 'sees' the code of the procedure
> > and tries to execute it.) so what else could i try?
> >
> > > And remember to do
> > >  RETURN new
> > > somewhere.
> >
> > right. but now i have a different problem... :)
>
>     Can you add the line
>
>         #option dump
>
>     at the very top of your function (before DECLARE) and send me
>     the debug output from the Postmaster?

sure! if you like, i can post the source SQL for the ratings
table and its views and functions and triggers and rules...

-- begin LOGFILE --

Execution tree of successfully compiled PL/pgSQL function _rating_propagate:

Functions data area:
    entry 0: REC new
    entry 1: REC old
    entry 2: VAR tg_name          type name (typoid 19) atttypmod -1
    entry 3: VAR tg_when          type text (typoid 25) atttypmod -1
    entry 4: VAR tg_level         type text (typoid 25) atttypmod -1
    entry 5: VAR tg_op            type text (typoid 25) atttypmod -1
    entry 6: VAR tg_relid         type oid (typoid 26) atttypmod -1
    entry 7: VAR tg_relname       type name (typoid 19) atttypmod -1
    entry 8: VAR tg_nargs         type int4 (typoid 23) atttypmod -1
    entry 9: VAR found            type bool (typoid 16) atttypmod -1
    entry 10: VAR opinion          type bpchar (typoid 1042) atttypmod 5
    entry 11: VAR courseID         type int4 (typoid 23) atttypmod -1
    entry 12: VAR topicID          type int4 (typoid 23) atttypmod -1
    entry 13: VAR eduID            type int4 (typoid 23) atttypmod -1
    entry 14: VAR profID           type int4 (typoid 23) atttypmod -1
    entry 15: RECFIELD rating           of REC 0
    entry 16: ROW *internal*       fields courseID=var 11
    entry 17: RECFIELD course           of REC 0
    entry 18: ROW *internal*       fields topicID=var 12
    entry 19: ROW *internal*       fields eduID=var 13
    entry 20: ROW *internal*       fields profID=var 14
    entry 21: RECFIELD who              of REC 0
    entry 22: RECFIELD student          of REC 0
    entry 23: RECFIELD who              of REC 0
    entry 24: RECFIELD student          of REC 0
    entry 25: RECFIELD who              of REC 0
    entry 26: RECFIELD student          of REC 0
    entry 27: RECFIELD who              of REC 0
    entry 28: RECFIELD student          of REC 0
    entry 29: RECFIELD who              of REC 0
    entry 30: RECFIELD rating           of REC 0
    entry 31: RECFIELD student          of REC 0
    entry 32: RECFIELD who              of REC 0
    entry 33: RECFIELD who              of REC 0
    entry 34: RECFIELD student          of REC 0

Functions statements:
  8:BLOCK <<*unnamed*>>
  9:  ASSIGN var 10 := 'SELECT  upper(substring( $1  from 1 for 1)) {$1=15}'
 10:  SELECT 'SELECT  _course.id WHERE _course.id =  $1  {$1=17}'
        target = 16 *internal*
 12:  SELECT 'SELECT  _topic.id WHERE _topic.id = _course.topic AND _course.id =  $1  {$1=11}'
        target = 18 *internal*
 14:  SELECT 'SELECT  _edu.id WHERE _edu.id = _topic.edu AND _topic.id =  $1  {$1=12}'
        target = 19 *internal*
 17:  SELECT 'SELECT  _prof.who WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=21, $2=11}'
        target = 20 *internal*
 19:  IF 'SELECT  NOT  $1  {$1=9}' THEN
 20:    RAISE ''Unable to match professor to class, for rating''
      ELSE
      ENDIF
 22:  IF 'SELECT   $1  = 'A' {$1=10}' THEN
 24:    EXECSQL 'UPDATE _course SET a = a + 1 WHERE _course.id =  $1  {$1=11}'
 25:    EXECSQL 'UPDATE _topic SET a = a + 1 WHERE _topic.id =  $1  {$1=12}'
 26:    EXECSQL 'UPDATE _edu SET a = a + 1 WHERE _edu.id =  $1  {$1=13}'
 27:    EXECSQL 'UPDATE _student SET a = a + 1 WHERE _student.who =  $1  {$1=22}'
 28:    EXECSQL 'UPDATE _faculty SET a = a + 1 WHERE _faculty.who =  $1  AND _faculty.edu =  $2  {$1=23, $2=13}'
 29:    EXECSQL 'UPDATE _prof SET a = a + 1 WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=14, $2=11}'
      ELSE
 32:    IF 'SELECT   $1  = 'C' {$1=10}' THEN
 33:      EXECSQL 'UPDATE _course SET c = c + 1 WHERE _course.id =  $1  {$1=11}'
 34:      EXECSQL 'UPDATE _topic SET c = c + 1 WHERE _topic.id =  $1  {$1=12}'
 35:      EXECSQL 'UPDATE _edu SET c = c + 1 WHERE _edu.id =  $1  {$1=13}'
 36:      EXECSQL 'UPDATE _student SET c = c + 1 WHERE _student.who =  $1  {$1=24}'
 37:      EXECSQL 'UPDATE _faculty SET c = c + 1 WHERE _faculty.who =  $1  AND _faculty.edu =  $2  {$1=25, $2=13}'
 38:      EXECSQL 'UPDATE _prof SET c = c + 1 WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=14, $2=11}'
        ELSE
 41:      IF 'SELECT   $1  = 'D' {$1=10}' THEN
 42:        EXECSQL 'UPDATE _course SET d = d + 1 WHERE _course.id =  $1  {$1=11}'
 43: Couldn't open /var/lib/postgres/data/pg_options
010304.13:07:18.501 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_student"
010304.13:07:18.513 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_student"
010304.13:07:18.519 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_course"
010304.13:07:18.533 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_course"
010304.13:07:18.547 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_who"
010304.13:07:18.566 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_who"
010304.13:07:18.577 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "l_rating"
010304.13:07:18.590 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "l_rating"
010304.13:07:18.684 [15216] NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index '_rating_pkey' for table
'_rating'
010304.13:07:18.684 [15216] NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
       EXECSQL 'UPDATE _topic SET d = d + 1 WHERE _topic.id =  $1  {$1=12}'
 44:        EXECSQL 'UPDATE _edu SET d = d + 1 WHERE _edu.id =  $1  {$1=13}'
 45:        EXECSQL 'UPDATE _student SET d = d + 1 WHERE _student.who =  $1  {$1=26}'
 46:        EXECSQL 'UPDATE _faculty SET d = d + 1 WHERE _faculty.who =  $1  AND _faculty.edu =  $2  {$1=27, $2=13}'
 47:        EXECSQL 'UPDATE _prof SET d = d + 1 WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=14, $2=11}'
          ELSE
 50:        IF 'SELECT   $1  = 'F' {$1=10}' THEN
 51:          EXECSQL 'UPDATE _course SET f = f + 1 WHERE _course.id =  $1  {$1=11}'
 52:          EXECSQL 'UPDATE _topic SET f = f + 1 WHERE _topic.id =  $1  {$1=12}'
 53:          EXECSQL 'UPDATE _edu SET f = f + 1 WHERE _edu.id =  $1  {$1=13}'
 54:          EXECSQL 'UPDATE _student SET f = f + 1 WHERE _student.who =  $1  {$1=28}'
 55:          EXECSQL 'UPDATE _faculty SET f = f + 1 WHERE _faculty.who =  $1  AND _faculty.edu =  $2  {$1=29, $2=13}'
 56:          EXECSQL 'UPDATE _prof SET f = f + 1 WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=14, $2=11}'
            ELSE
 58:          ASSIGN var 30 := 'SELECT  'B''
 60:          EXECSQL 'UPDATE _course SET b = b + 1 WHERE _course.id =  $1  {$1=11}'
 61:          EXECSQL 'UPDATE _topic SET b = b + 1 WHERE _topic.id =  $1  {$1=12}'
 62:          EXECSQL 'UPDATE _edu SET b = b + 1 WHERE _edu.id =  $1  {$1=13}'
 63:          EXECSQL 'UPDATE _student SET b = b + 1 WHERE _student.who =  $1  {$1=31}'
 64:          EXECSQL 'UPDATE _faculty SET b = b + 1 WHERE _faculty.who =  $1  AND _faculty.edu =  $2  {$1=32, $2=13}'
 65:          EXECSQL 'UPDATE _prof SET b = b + 1 WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=14, $2=11}'
            ENDIF
          ENDIF
        ENDIF
      ENDIF
 70:  EXECSQL 'delete from trak'
 71:  EXECSQL 'insert into trak values( $1 , $2 , $3 , $4 , $5 ) {$1=13, $2=12, $3=11, $4=33, $5=34}'
 72:  RETURN record 0
    END -- *unnamed*

End of execution tree of function _rating_propagate

-- end LOGFILE --

of course, these tables are intricately related to others not
mentioned here... but i'm hoping you can rub your eyeballs over
this and think to yourself "what a newbie! if he'd only ditch the
clavis and use the frammistat, it'd be so simple..." :)

if so, bring it on!

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
        -- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!